Transfer data from one table to another identical table-CodePudding

SET IDENTITY_INSERT [db1].[dbo].Subscriber ON

INSERT INTO [db1].[dbo].Subscriber
    SELECT * FROM [db2].[dbo].SubScriber

PRINT 'Successfully Re-imported data from SubScriber backup'

SET IDENTITY_INSERT [db1].[dbo].Subscriber OFF

All I want is a dynamic way to copy data from one table that has an identical setup as another table in another database but I continue to get this error:

An explicit value for the identity column in table 'db1.dbo.MsSubProject' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I have already explicitly set the IDENTITY_INSERT to ON. Also, even when I try explicitly write out the table columns like so:

SET IDENTITY_INSERT [db1].[dbo].Subscriber ON

INSERT INTO [db1].[dbo].Subscriber (SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID, SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail, SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted)
    SELECT * FROM [db2].[dbo].SubScriber

PRINT 'Successfully Re-imported data from SubScriber backup'

SET IDENTITY_INSERT [db1].[dbo].MsSubscriber OFF

This also doesn't work...

What am I doing wrong? Every solution I've found on StackOverflow for this specific issue doesn't work for me.

CodePudding user response：

You should also get in the habit of using explicit column lists in your SELECT statements! SELECT * is OK for querying ad-hoc style in SSMS - but it should be banned from use in any production code!

Try this:

SET IDENTITY_INSERT [db1].[dbo].Subscriber ON

INSERT INTO [db1].[dbo].Subscriber (SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID, SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail, SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted)
    SELECT 
        SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID, 
        SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail, 
        SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted
    FROM [db2].[dbo].SubScriber

PRINT 'Successfully Re-imported data from SubScriber backup'

SET IDENTITY_INSERT [db1].[dbo].MsSubscriber OFF

CodePudding user response：

If you really wanted to get out of writing the column list, you could use dynamic SQL

DECLARE @cols1 nvarchar(max), @cols2 nvarchar(max);

SELECT
    @cols1 = STRING_AGG(QUOTENAME(c1.name, ',')) WITHIN GROUP (ORDER BY c1.name),
    @cols2 = STRING_AGG(QUOTENAME(c2.name, ',')) WITHIN GROUP (ORDER BY c1.name) -- same order
FROM db1.sys.columns c1
JOIN db2.sys.columns c2
WHERE c1.object_id = OBJECT_ID(N'[db1].[dbo].Subscriber')
  AND c2.object_id = OBJECT_ID(N'[db2].[dbo].Subscriber');

DECLARE @sql nvarchar(max) = N'
SET IDENTITY_INSERT [db1].[dbo].Subscriber ON;

INSERT INTO [db1].[dbo].Subscriber
('   @cols1   N')
    SELECT 
        '   @cols2   N'
    FROM [db2].[dbo].SubScriber;

PRINT ''Successfully Re-imported data from SubScriber backup'';

SET IDENTITY_INSERT [db1].[dbo].Subscriber OFF;
';

EXEC sp_executesql @sql;