Productivity tips, reviews, tools, software and gadgets.

How-To: Copy SQL table with identity to other database
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

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

SET IDENTITY_INSERT [D:\Db\Target.MDF].dbo.Languages OFF

Note the use of IDENTITY_INSERT and the list of columns before the SELECT statement.

Labels: , ,

  Posted by oVan on Wednesday, May 28, 2008 | PermaLink | 0 comments
« SuperWasp start page