If you ever wanted to copy a database table with contents to a different database, including the identity columns used for primary and foreign key relations, you might encounter an error message similar to this:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'D:\Db\Target.MDF.dbo.Languages' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Here is the working SQL Query code to solve this problem:
SET IDENTITY_INSERT [D:\Db\Target.MDF].dbo.Languages ON
GO
INSERT INTO [D:\Db\Target.MDF].dbo.Languages (LanguageID, LanguageCodeID, CountryID, NameType, Name)
SELECT LanguageID, LanguageCodeID, CountryID, NameType, [Name]
FROM [D:\Db\Source.MDF].dbo.Languages AS SourceTable
GO
SET IDENTITY_INSERT [D:\Db\Target.MDF].dbo.Languages OFF
GO
Note the use of IDENTITY_INSERT and the list of columns before the SELECT statement.
Labels: how-to, SQL, tip
