Transfer data from one table to another identical table

November 16, 2021

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.

>Solution :

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