Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Generate Insert Statements

Total Hit ( 6678)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


Click here to copy the following block
/*
This script will generate insert statements for the given tables. You can pass
the tables names into DataAsInsCommand stored procedure separated by commas,
as in the example below:

EXEC DataAsInsCommand 'employee,titleauthor,pub_info'

Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This script will generate insert statements for the given tables.
You can pass the tables names into DataAsInsCommand stored procedure
separated by commas, as in the example below:

EXEC DataAsInsCommand 'employee,titleauthor,pub_info'

It can be used if you want to transfer your data at the new place.
You can first generate script with the tables creation and after that
run the results set of the DataAsInsCommand stored procedure to
insert rows. By the way, more proper way to do it - is generate DTS package.
My stored procedure can be used for learning some general SQL Server
features (how to work with string functions, how to work with unknown
number of parameters, passed into stored procedure, how to work with
cursors and so on).
*/


IF OBJECT_ID('DataAsInsCommand') IS NOT NULL DROP PROC DataAsInsCommand
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC DataAsInsCommand (
 @TableList varchar (200))
AS
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (2000), @TableName varchar (30)
DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint
SELECT @TableList = @TableList + ','
SELECT @IsIdentity = 0
SELECT @position = PATINDEX('%,%', @TableList)
WHILE (@position <> 0)
 BEGIN
  SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
  SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList), '')
  SELECT @position = PATINDEX('%,%', @TableList)

  SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR '
   + 'SELECT name, xtype, status FROM syscolumns WHERE id = object_id("'
   + @TableName + '")'
  EXEC (@exec_str)

  OPEN fetch_cursor
  FETCH fetch_cursor INTO @name, @xtype, @status
  IF (@status & 0x80) <> 0
   BEGIN
    SELECT @IsIdentity = 1
    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
    SELECT 'GO'
   END
  SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "

  --text or ntext
  IF (@xtype = 35) OR (@xtype = 99)
    SELECT @exec_str = @exec_str + '''"None yet"'''
  ELSE

  --image
  IF (@xtype = 34)
    SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
  ELSE

  --smalldatetime or datetime
  IF (@xtype = 58) OR (@xtype = 61)
    SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
  ELSE

  --varchar or char or nvarchar or nchar
  IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
    SELECT @exec_str = @exec_str + '''"'' + ' + @name + ' + ''"'''
  ELSE

  --uniqueidentifier
  IF (@xtype = 36)
    SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
  ELSE

  --binary or varbinary
  IF (@xtype = 173) OR (@xtype = 165)
    SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
  ELSE

    SELECT @exec_str = @exec_str + 'ISNULL(CONVERT(varchar,' + @name + '), "null")'

  WHILE @@FETCH_STATUS <> -1
   BEGIN
    FETCH fetch_cursor INTO @name, @xtype, @status
    IF (@@FETCH_STATUS = -1) BREAK
    IF (@status & 0x80) <> 0
     BEGIN
      SELECT @IsIdentity = 1
      SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
      SELECT 'GO'
     END

    --text or ntext
    IF (@xtype = 35) OR (@xtype = 99)
      SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''
    ELSE

    --image
    IF (@xtype = 34)
      SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0xFFFFFFFF' + '"'
    ELSE

    --smalldatetime or datetime
    IF (@xtype = 58) OR (@xtype = 61)
      SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
    ELSE

    --varchar or char or nvarchar or nchar
    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
      SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"'' + ' + @name + ' + ''"'''
    ELSE

    --uniqueidentifier
    IF (@xtype = 36)
      SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
    ELSE

    --binary or varbinary
    IF (@xtype = 173) OR (@xtype = 165)
      SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' + '"'
    ELSE

      SELECT @exec_str = @exec_str + ' + ","' + ' + ISNULL(CONVERT(varchar,' + @name + '), "null")'
   END

  CLOSE fetch_cursor
  DEALLOCATE fetch_cursor

  SELECT @exec_str = @exec_str + '+ ")" FROM ' + @TableName
  SELECT @exec_str
  EXEC(@exec_str)
  SELECT 'GO'

  IF @IsIdentity = 1
    BEGIN
     SELECT @IsIdentity = 0
     SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
     SELECT 'GO'
    END
 END
GO


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.