SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate INSERT for table with IDENTITY column


Generate INSERT for table with IDENTITY column

Author
Message
Jesse McLain
Jesse McLain
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 567
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
zubamark
zubamark
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4283 Visits: 3648
Displaying help information about the stored procedure when someone passes in 'help' as the first parameter is a nice touch.
Reginald J Ray Jr
Reginald J Ray Jr
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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
Gary Keith Lindsey
Gary Keith Lindsey
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 78
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.
Jesse McLain
Jesse McLain
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 567
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!

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Gordo-174357
Gordo-174357
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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 ''
Gordo-174357
Gordo-174357
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search