Generate Insert Statements Easily

,

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

EXEC sp_DataAsInsCommand 'employee,titleauthor,pub_info'

Updated on 5/25/01 to correct an issue with columns that are short like a column defined as char(1), to correct another issue with the Identity column (before you had to supply a list of columns in the insert statement), and to add the following two changes: 1) to print the name of the table in a comment before the insert commands, and 2) to expand the size of the parameter so a larger list of tables could be submitted to the stored procedure.

CREATE PROC sp_DataAsInsCommand (
  @TableList varchar (8000))
AS
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (2000), @TableName varchar (50)
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 a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @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 (' + "
    Select ' -- The table name is: ' + @TableName
    --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 + 'Coalesce(' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
    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 + 'Coalesce(' + '''"'' + ' + @name + ' + ''"''' + ',"null")'
    ELSE

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

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

        SELECT @exec_str = @exec_str + 'Coalesce(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 + ' + ","' + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
        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 + ' + ","' + ' + Coalesce(''"'' + ' + @name + ' + ''"''' + ',"null")'
        ELSE

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

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

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

    CLOSE fetch_cursor
    DEALLOCATE fetch_cursor

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

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

Rate

3 (9)

Share

Share

Rate

3 (9)