Generate INSERT for table with IDENTITY column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Generate INSERT for table with IDENTITY...

Generate INSERT for table with IDENTITY column

Rate Topic

Display Mode

Topic Options

Author

Message

Jesse McLain

Posted Sunday, January 20, 2008 2:21 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552

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

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com

Post #445273

zubamark

Posted Friday, March 07, 2008 6:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 9:48 AM
Points: 45, Visits: 71

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

Post #465792

Cliff Jones

Posted Friday, March 07, 2008 7:27 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390, Visits: 3,403

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

Post #465830

Reginald J Ray Jr

Posted Friday, March 07, 2008 8:38 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, Visits: 55

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

Post #465924

Gary Keith Lindsey

Posted Friday, March 07, 2008 8:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:44 AM
Points: 1,080, Visits: 74

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.

Post #465939

Jesse McLain

Posted Thursday, March 20, 2008 11:11 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552

Reginald J Ray Jr (3/7/2008)

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!

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com

Post #472428

Gordo-174357

Posted Tuesday, May 22, 2012 12:55 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:46 PM
Points: 19, Visits: 68

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 ''

Post #1304429

Gordo-174357

Posted Tuesday, May 22, 2012 1:09 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:46 PM
Points: 19, Visits: 68

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).

Post #1304440

« Prev Topic | Next Topic »

Permissions