I am trying to build a dynamic insert command from one database to another. The destination database is identical to the source database, but the tables in the destination database are blank. I created it with a script to create all tables/views/sprocs from source db. I know want to create this script to insert from the source db into the destination db. My insert works, but I need to add a "Set Identity_Insert ON" for the tables with identity columns. I have about 8 of them. How can I edit my code below to add the Set Identity_Insert ON where it is needed?
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'INSERT INTO [UNFI_WEST_F_Compressed].dbo.' + QUOTENAME(name)
+ N' SELECT * FROM [UNFI_WEST_F].dbo.' + QUOTENAME(name) + N' WITH(TABLOCK) ;' + CHAR(13)
FROM sys.tables
PRINT @sql; -- this will only print 8K, enough to spot check
--EXEC [destination_linked_server].master.sys.sp_executesql @sql;
EXEC sp_executesql @sql;
Select Object_Name([object_id]) as [Table Name]
,[name] as [Column Name]
,is_identity
from sys.columns
Where is_identity=1 And Objectproperty(object_id,'IsUserTable')=1 --AND Object_Name([object_id]) = 'MainVdtl_COMBINED_4_ADJ'
Thanks in advance!
Alex
November 28, 2019 at 9:19 am
There are probably several ways of doing this. One approach is something like this:
DECLARE @sql NVARCHAR(MAX) = N'';
;with tabsWithId as (select t.Name
, 'set identity_insert [UNFI_WEST_F_Compressed].dbo.' + t.name + ' on; ' idOn
, 'set identity_insert [UNFI_WEST_F_Compressed].dbo.' + t.name + ' off; ' idOff
from [UNFI_WEST_F_Compressed].sys.tables t
where exists (select null
from [UNFI_WEST_F_Compressed].sys.columns c
where c.object_id = t.object_id
and c.is_identity = 1
)
)
SELECT @sql += isnull(tabsWithId.idOn, '')
+ N'INSERT INTO [UNFI_WEST_F_Compressed].dbo.' + QUOTENAME(t.name)
+ N' SELECT * FROM [UNFI_WEST_F].dbo.' + QUOTENAME(t.name) + N' WITH(TABLOCK) ;'
+ isnull(tabsWithId.idOff, '')
+ CHAR(13)
FROM sys.tables t
left outer join tabsWithId on tabsWithId.name = t.name
PRINT @sql; -- this will only print 8K, enough to spot check
November 28, 2019 at 11:34 am
There are probably several ways of doing this. One approach is something like this:
Why omit QUOTENAME in the initial statement?
Personally, I would do it like this, and not just be specific about the table name, but the schema and columns too:
USE UNFI_WEST_F;
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'USE UNFI_WEST_F;' + @CRLF + @CRLF +
STUFF((SELECT @CRLF + @CRLF +
N'PRINT N''Inserting data for table ' + REPLACE(s.[name],'''','''''''') + N'.' + REPLACE(t.[name],'''','''''''') + N''';' + @CRLF +
N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' ON;' + @CRLF +
N'INSERT INTO ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' (' + C.ColumnList + N')' + @CRLF +
N'SELECT ' + C.ColumnList + @CRLF +
N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';' + @CRLF +
N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' OFF;'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
CROSS APPLY ((SELECT STUFF((SELECT N',' + QUOTENAME(c.[name])
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') AS ColumnList)) C
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');
PRINT @SQL;
EXEC sp_executesql @SQL;
On one of the sandbox servers, this ends up with a statement that looks like this:
USE UNFI_WEST_F;
PRINT N'Inserting data for table dbo.SNTemp';
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[SNTemp] ON;
INSERT INTO [dbo].[SNTemp] ([SerialNumber],[iStockID],[SNDateLMove],[CurrentLoc],[CurrentAccLink],[iSNLotID],[iSNMFPID],[iSNMFPLineID],[SerialMF_iBranchID],[fQtyOut])
SELECT [SerialNumber],[iStockID],[SNDateLMove],[CurrentLoc],[CurrentAccLink],[iSNLotID],[iSNMFPID],[iSNMFPLineID],[SerialMF_iBranchID],[fQtyOut]
FROM [dbo].[SNTemp];
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[SNTemp] OFF;
PRINT N'Inserting data for table dbo.FirstTable';
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[FirstTable] ON;
INSERT INTO [dbo].[FirstTable] ([Id],[columnName],[isHidden])
SELECT [Id],[columnName],[isHidden]
FROM [dbo].[FirstTable];
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[FirstTable] OFF;
PRINT N'Inserting data for table dbo.rCTE_Vs_Tally';
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[rCTE_Vs_Tally] ON;
INSERT INTO [dbo].[rCTE_Vs_Tally] ([RunID],[TestName],[rCTETally],[RowsGenerated],[SetsUsed],[StartTime],[EndTime],[TimeTaken])
SELECT [RunID],[TestName],[rCTETally],[RowsGenerated],[SetsUsed],[StartTime],[EndTime],[TimeTaken]
FROM [dbo].[rCTE_Vs_Tally];
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[rCTE_Vs_Tally] OFF;
PRINT N'Inserting data for table dbo.Letters';
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[Letters] ON;
INSERT INTO [dbo].[Letters] ([Letter])
SELECT [Letter]
FROM [dbo].[Letters];
SET IDENTITY_INSERT UNFI_WEST_F_Compressed.[dbo].[Letters] OFF;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 28, 2019 at 12:43 pm
Why omit
QUOTENAMEin the initial statement?
An oversight.
Yours is probably the more complete solution from a generic perspective, including columns etc. My approach was based on the OP's code which had hard coded dbo, no colums so I stuck with that. Some people just don't feel the need to use multiple schemas for a particular scenario.
One thing I am slightly curious about is how (whether?) you are only including the identity insert on/off for those tables which actually have an identity column.
November 28, 2019 at 12:49 pm
An oversight.
Fair enough, we all do them. Took me 20 minutes to work out that i'd typed 2 instead of 1 in a statement earlier, and couldn't for the life of me work out why it was failing >_<
Some people just don't feel the need to use multiple schemas for a particular scenario.
True, and with the OP's scenario, hard coded dbo hopefully isn't an issue. I'm just in the habit now of making sure that dynamic SQL I use does define the schema and table names (and database), even if for nothing more than future proofing.
One thing I am slightly curious about is how (whether?) you are only including the identity insert on/off for those tables which actually have an identity column.
It doesn't, no, and you raise a good point. I've optimistically assumed that all of the OP's table have a column with the IDENTITY property. I (probably) shouldn't..
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 28, 2019 at 5:10 pm
SQLian,
How do I add the field names to your code? I tried Thom's but Not all my tables have identity fields...in fact they are only 6 out of 30 in 1 database. In your code, the identity_Insert command only works when you list out the column names. so I need to list out the column names.
November 28, 2019 at 6:15 pm
SQLian,
The msg I get when I run your code is this:
Table 'UNFI_WEST_F_Compressed.dbo.UNFI_LI_SHORT' does not have the identity property. Cannot perform SET operation.
It is picking out a table without an identity column.
Where do you filter for the tables with identity columns? I can't seem to find it in your code.
November 28, 2019 at 6:21 pm
SQLian,
How do I add the field names to your code? I tried Thom's but Not all my tables have identity fields...in fact they are only 6 out of 30 in 1 database. In your code, the identity_Insert command only works when you list out the column names. so I need to list out the column names.
SQLian,
forget about this msg...I had several tabs opened in my sql studio and I was testing and editing things and commented on the wrong thing. Although Thom's code would work also, if it could pick out only the tables with identity columns. It is selecting all tables and adding this code to it.
November 29, 2019 at 2:51 am
This was removed by the editor as SPAM
November 29, 2019 at 3:15 am
Thom,
Thanks for getting me 99% therre, but I'm not getting the list of the field names, just 1 field name appears in the list on the insert and select side. I added this line "AND c.is_identity = 1 " to filter for the tables with an identity column. If I take this line out, I get all the columns but it also does not filter for the tables with identity columns...It also gives me tables without identity columns in them.
USE UNFI_WEST_F;
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'USE UNFI_WEST_F;' + @CRLF + @CRLF +STUFF(
(SELECT @CRLF + @CRLF +N'PRINT N''Inserting data for table ' + REPLACE(s.[name],'''','''''''') + N'.' + REPLACE(t.[name],'''','''''''') + N''';' + @CRLF +N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' ON;' + @CRLF +N'INSERT INTO ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' (' + C.ColumnList + N')' + @CRLF +N'SELECT ' + C.ColumnList + @CRLF +N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';' + @CRLF +N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' OFF;'
FROM sys.schemas s JOIN sys.tables t
ON s.schema_id = t.schema_id
CROSS APPLY (
(SELECT STUFF(
(SELECT N',' + QUOTENAME(c.[name])
FROM sys.columns cWHERE c.object_id = t.object_id
AND c.is_identity = 1 FOR XML PATH(N''),
TYPE
)
.value('.','nvarchar(MAX)'),1,1,N'') AS ColumnList
)
) C
FOR XML PATH(N''),TYPE.value('.','nvarchar(MAX)'),1,4,N'') ;
PRINT @SQL;--EXEC sp_executesql @SQL;
November 29, 2019 at 9:19 am
GrassHopper,
Here is a version of my query with columns added. Bit of a rush job so may need to check it. It still doesn't do the schemas though. Oh, and it's pretty obvious I lifted the column code from Thom (ahem).
DECLARE @sql NVARCHAR(MAX) = N'';
declare @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
;with tabsWithId as (select t.Name
, 'set identity_insert [UNFI_WEST_F_Compressed].dbo.' + quotename(t.name) + ' on; ' + @CRLF idOn
, 'set identity_insert [UNFI_WEST_F_Compressed].dbo.' + quotename(t.name) + ' off; ' + @CRLF idOff
from [UNFI_WEST_F_Compressed].sys.tables t
where exists (select null
from [UNFI_WEST_F_Compressed].sys.columns c
where c.object_id = t.object_id
and c.is_identity = 1
)
)
, cols as (select t.Name
, c.ColumnList
from sys.tables t
CROSS APPLY ((SELECT STUFF((SELECT N',' + QUOTENAME(c.[name])
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') AS ColumnList)) C
)
SELECT @sql += isnull(tabsWithId.idOn, '')
+ N'INSERT INTO [UNFI_WEST_F_Compressed].dbo.' + QUOTENAME(t.name) + '(' + cols.ColumnList + ')' + @CRLF
+ N'SELECT ' + cols.ColumnList + @CRLF + 'FROM [UNFI_WEST_F].dbo.' + QUOTENAME(t.name) + N' WITH(TABLOCK) ;' + @CRLF
+ isnull(tabsWithId.idOff, '')
+ CHAR(13)
FROM sys.tables t
inner join cols on cols.name = t.name
left outer join tabsWithId on tabsWithId.name = t.name
PRINT @sql; -- this will only print 8K, enough to spot check
Updated to no longer assume, all tables have an IDENTITY property column:
USE UNFI_WEST_F;
GO
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'USE UNFI_WEST_F;' + @CRLF + @CRLF +
STUFF((SELECT @CRLF + @CRLF +
N'PRINT N''Inserting data for table ' + REPLACE(s.[name],'''','''''''') + N'.' + REPLACE(t.[name],'''','''''''') + N''';' + @CRLF +
CASE WHEN i.HasIdentity = 1 THEN N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' ON;' + @CRLF ELSE N'' END +
N'INSERT INTO UNFI_WEST_F.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' (' + C.ColumnList + N')' + @CRLF +
N'SELECT ' + C.ColumnList + @CRLF +
N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';' + @CRLF +
CASE WHEN i.HasIdentity = 1 THEN N'SET IDENTITY_INSERT UNFI_WEST_F_Compressed.' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' OFF;' ELSE N'' END
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
CROSS APPLY ((SELECT STUFF((SELECT N',' + QUOTENAME(c.[name])
FROM sys.columns c
WHERE c.object_id = t.object_id
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') AS ColumnList)) C
CROSS APPLY (SELECT MAX(1) AS HasIdentity
FROM sys.identity_columns ic
WHERE ic.object_id = t.object_id) i
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');
PRINT @SQL;
--EXEC sp_executesql @SQL;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 29, 2019 at 2:01 pm
SQLian and Thom, Thank you! both. I wish i could set both to the answer but it only lets me set 1 of them. I've learned something from both of your solutions.
You guys are life savers...this saves me a lot of manual work. I have to Create an empty database and create a copy of the structure of another database and then insert the data into compressed tables. We are trying to cut down the amount space we are using and at same time get rid of the fragmentation on the db's. So I'm killing 2 birds with 1 stone.
Now I just have to figure out how to script out the permissions from source db so I can run it on my destination db.
December 5, 2019 at 11:45 pm
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply