Generate INSERT for table with IDENTITY column

  • Comments posted to this topic are about the item Generate INSERT for table with IDENTITY column

  • Won't be easy to exclude identity column from insert statement?

    existing table T1 (ID [int] IDENTITY(1,1) NOT NULL,

    A1 varchar(50),A2 varchar(50),A3 varchar(50)......)

    insert into T1 (A1,A2,A3,...)

    select B1,B2,B3,... from T2

  • Displaying help information about the stored procedure when someone passes in 'help' as the first parameter is a nice touch.

  • This didn't work for me initially. It duplicated a column four times. I had to modify the code as below:

    Original line:

    WHERE O1.Name = @Target_Table) AS C1

    New line:

    WHERE O1.Name = @Target_Table and T1.Name <> 'sysname') AS C1

    Original line:

    WHERE O2.Name = @Source_Table) AS C2

    New line:

    WHERE O2.Name = @Source_Table and T2.Name <> 'sysname') AS C2

    With those changes it worked. Will be a time-saver. 'Message' was the column name that was duplicated.

    Thanks

  • This works great if you know the columns and it's only one table. Next task is to dynamically generate the insert and select statements dynamically from the columns of any given table so that we can generate the insert command for any number of tables while in a loop. Using 5 separate stored procedures to start the insert, concatenate the insert, end the insert and start the select, concatenate the select and end the select we can read the column list into a temporary table then loop through the columns and build the command.

  • Reginald J Ray Jr (3/7/2008)


    This didn't work for me initially. It duplicated a column four times. I had to modify the code as below:

    Original line:

    WHERE O1.Name = @Target_Table) AS C1

    New line:

    WHERE O1.Name = @Target_Table and T1.Name <> 'sysname') AS C1

    Original line:

    WHERE O2.Name = @Source_Table) AS C2

    New line:

    WHERE O2.Name = @Source_Table and T2.Name <> 'sysname') AS C2

    With those changes it worked. Will be a time-saver. 'Message' was the column name that was duplicated.

    Thanks

    Yes, SQL Server will make multiple entries in SysColumns for some columns, causing the duplicate lines in the generated script. I put your fix in place, along with a "DISTINCT" clause on the subqueries. Thanks for your response!

  • Here is a more polished version of the script, enhancements: bracket column names to allow for spaces, add ability to tack on a prefix - this lets you generate a script for doing inserts from another server, add a GO to the output so you can monitor progress when running a large batch, add handling for table names passed in with brackets and [dbo]. which is the case when using sp_MSforeachtable. Only generate enable identity_insert statements IF the table actually has an identity column.

    --Example Call: For every table generate a truncate/insert

    exec sp_MSforeachtable @command1="exec [spd_Build_Insert_Into_Stmt_With_Identity] '?','?','[LINKED_SERVER_NAME].DATABASENAME.'"

    Create PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]

    @Source_Table varchar(200),-- req'd; the name of the source table

    @Target_Table varchar(200) = '',-- req'd; the name of the target table

    @FromPrefix varchar(500)='',-- String that will be added to the from statement, this lets you use linked server data sources

    @Print_Truncate char(1) = 'Y',-- optional; 'Y' to print truncate statement

    @Print_Identity_Insert_Stmts char(1) = 'Y',-- optional; 'Y' to include the SET IDENTITY_INSERT

    -- statements in the output

    @Show_Matchless_Columns char(1) = 'N',-- optional; 'Y' to include the columns from each

    -- table that have no match in the other table (if

    -- the tables have slightly different structures);

    -- these columns will be commented-out in output

    @AddGo char(1) = 'Y'-- Add a GO to the end which will allow you to view progress when a batch is running

    AS

    IF @Source_Table = 'help'

    BEGIN

    PRINT 'PROCEDURE [dbo].[spd_Build_Insert_Into_Stmt_With_Identity]'

    PRINT '@Source_Table varchar(200),-- required; the name of the source table '

    PRINT '@Target_Table varchar(200) = '''',-- required; the name of the target table '

    PRINT ' @FromPrefix varchar(500)='''',-- String that will be added to the from statement, this lets you use linked server data sources '

    PRINT ' @Print_Truncate char(1) = ''Y'',-- optional; ''Y'' to print truncate statement '

    PRINT '@Print_Identity_Insert_Stmts char(1) = ''Y'',-- optional; ''Y'' to include the SET IDENTITY_INSERT '

    PRINT '-- statements in the output'

    PRINT '@Show_Matchless_Columns char(1) = ''N''-- optional; ''Y'' to include the columns from each '

    PRINT '-- table that have no match in the other table (if '

    PRINT '-- the tables have slightly different structures);'

    PRINT '-- these columns will be commented-out in output'

    PRINT ' @AddGo char(1) = ''Y''-- Add a GO to the end which will allow you to view progress when a batch is running'

    RETURN

    END

    DECLARE @sql_into varchar(8000)

    DECLARE @sql_from varchar(8000)

    DECLARE @sql varchar(8000)

    DECLARE @crlf char(2)

    SET @crlf = CHAR(13) + CHAR(10)

    DECLARE @indent char(1)

    SET @indent = CHAR(9)

    DECLARE @ColName1 varchar(250)

    DECLARE @ColOrder1 smallint

    DECLARE @ColType1 varchar(50)

    DECLARE @ColLength1 smallint

    DECLARE @ColPrec1 smallint

    DECLARE @ColScale1 smallint

    DECLARE @IsNullable1 tinyint

    DECLARE @ColName2 varchar(250)

    DECLARE @ColOrder2 smallint

    DECLARE @ColType2 varchar(50)

    DECLARE @ColLength2 smallint

    DECLARE @ColPrec2 smallint

    DECLARE @ColScale2 smallint

    DECLARE @IsNullable2 tinyint

    DECLARE @OrderBy decimal(9,1)

    DECLARE Table_Struct_Compare_Cursor CURSOR FOR

    SELECT

    ColName1,

    ColOrder1,

    ColType1,

    ColLength1,

    ColPrec1,

    ColScale1,

    IsNullable1,

    ColName2,

    ColOrder2,

    ColType2,

    ColLength2,

    ColPrec2,

    ColScale2,

    IsNullable2,

    OrderBy = ISNULL(CONVERT(decimal(9,1), ColOrder1), CONVERT(decimal(9,1), ColOrder2) + 0.5)

    FROM

    (SELECT DISTINCT

    ColName1 = C.Name,

    ColOrder1 = C.ColOrder,

    ColType1 = T1.Name,

    ColLength1 = C.Length,

    ColPrec1 = C.XPrec,

    ColScale1 = C.XScale,

    IsNullable1 = C.IsNullable

    FROM SysColumns C

    JOIN SysObjects O1 ON O1.Id = C.Id

    JOIN SysTypes T1 ON T1.XType = C.XType

    WHERE O1.Name = REPLACE(REPLACE(REPLACE(@Target_Table,'[dbo].',''),'[',''),']','') and T1.Name <> 'sysname') AS C1

    FULL OUTER JOIN

    (SELECT DISTINCT

    ColName2 = C.Name,

    ColOrder2 = C.ColOrder,

    ColType2 = T2.Name,

    ColLength2 = C.Length,

    ColPrec2 = C.XPrec,

    ColScale2 = C.XScale,

    IsNullable2 = C.IsNullable

    FROM SysColumns C

    JOIN SysObjects O2 ON O2.Id = C.Id

    JOIN SysTypes T2 ON T2.XType = C.XType

    WHERE O2.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']','') and T2.Name <> 'sysname') AS C2

    ON C2.ColName2 = C1.ColName1

    ORDER BY OrderBy, ColOrder1, ColOrder2

    -- init vars:

    SET @sql_into = 'INSERT INTO ' + @Target_Table + ' ('

    SET @sql_from = 'SELECT '

    SET @sql = ''

    OPEN Table_Struct_Compare_Cursor

    FETCH NEXT FROM Table_Struct_Compare_Cursor INTO

    @ColName1,

    @ColOrder1,

    @ColType1,

    @ColLength1,

    @ColPrec1,

    @ColScale1,

    @IsNullable1,

    @ColName2,

    @ColOrder2,

    @ColType2,

    @ColLength2,

    @ColPrec2,

    @ColScale2,

    @IsNullable2,

    @OrderBy

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @ColName1 IS NULL

    BEGIN

    IF @Show_Matchless_Columns = 'Y'

    BEGIN

    --SET @sql_into = @sql_into + @crlf + '--' + @indent + '<no matching column in ' + @Target_Table + ' for column ' + @ColName2 + '>,'

    SET @sql_into = @sql_into + @crlf + '--' + @indent + '<missing:[' + @ColName2 + ']>,'

    SET @sql_from = @sql_from + @crlf + '--' + @indent + '<' + @Source_Table + '.[' + @ColName2 + ']>,'

    END

    END

    ELSE IF @ColName2 IS NULL

    BEGIN

    IF @Show_Matchless_Columns = 'Y'

    BEGIN

    SET @sql_into = @sql_into + @crlf + '--' + @indent + '<' + @Target_Table + '.[' + @ColName1 + ']>,'

    --SET @sql_from = @sql_from + @crlf + '--' + @indent + '<no matching column in ' + @Source_Table + ' for column ' + @ColName1 + '>,'

    SET @sql_from = @sql_from + @crlf + '--' + @indent + '<missing: [' + @ColName1 + ']>,'

    END

    END

    ELSE IF @ColType1 <> @ColType2

    BEGIN

    SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'

    SET @sql_from = @sql_from + @crlf + @indent + 'CONVERT(' + '[' + @ColType1 + '], [' + @ColName2 + ']),'

    END

    ELSE

    BEGIN

    SET @sql_into = @sql_into + @crlf + @indent + '[' + @ColName1 + '],'

    SET @sql_from = @sql_from + @crlf + @indent + '[' + @ColName2 + '],'

    END

    FETCH NEXT FROM Table_Struct_Compare_Cursor INTO

    @ColName1,

    @ColOrder1,

    @ColType1,

    @ColLength1,

    @ColPrec1,

    @ColScale1,

    @IsNullable1,

    @ColName2,

    @ColOrder2,

    @ColType2,

    @ColLength2,

    @ColPrec2,

    @ColScale2,

    @IsNullable2,

    @OrderBy

    END

    CLOSE Table_Struct_Compare_Cursor

    DEALLOCATE Table_Struct_Compare_Cursor

    SET @sql_into = LEFT(@sql_into, LEN(RTRIM(@sql_into)) - 1) + ')' + @crlf-- remove trailing comma, add closing paren

    SET @sql_from = LEFT(@sql_from, LEN(@sql_from) - 1) + @crlf-- remove trailing comma

    SET @sql_from = @sql_from + 'FROM ' + @FromPrefix + @Source_Table + ';'+ @crlf

    PRINT 'select ''Started Populating: ' + @Target_Table + '''+ CONVERT(varchar,GETDATE(),108) ;'

    IF @AddGo = 'Y'

    PRINT 'GO '

    IF @Print_Truncate = 'Y'

    PRINT 'TRUNCATE TABLE ' + @Target_Table

    --If the table does not have an identity column, then do not print the set identity_insert statement which would cause an error

    if NOT exists (select 1 from sys.columns sc inner join sys.objects so on so.Object_Id = sc.Object_Id where sc.is_identity = 1 and so.Type = 'u' and so.Name = REPLACE(REPLACE(REPLACE(@Source_Table,'[dbo].',''),'[',''),']',''))

    set @Print_Identity_Insert_Stmts = 'N'

    IF @Print_Identity_Insert_Stmts = 'Y'

    PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' ON;'

    PRINT @sql_into

    PRINT @sql_from

    IF @Print_Identity_Insert_Stmts = 'Y'

    PRINT 'SET IDENTITY_INSERT ' + @Target_Table + ' OFF;'

    PRINT ''

    PRINT ''

  • I don't know how to post a properly formatted block of code in the forum, so I just submitted my revised version as a new script, title = 'Generate insert statements with column names for any or all tables' if anyone is interested (since it hasn't been approved yet, I can't post a link to it).

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply