Problems with IDENTITY_INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with IDENTITY_INSERT

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2008-03-27 : 13:20:55

Hey Everyone,

I'm trying to copy the data from one table to another while preserving the identity keys, but I can't seem to get past this error message. Here's the SQL:


SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview ON

INSERT INTO dbo.Cms_MenuItems_Preview
SELECT     Id, [Name], Url, Parent, IsActive, SortIndex
FROM       dbo.Cms_MenuItems

SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview OFF

and the error message is:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Cms_MenuItems_Preview' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Any suggestions?

Thanks in advance!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-27 : 13:44:41

The error message says you have to use a column list.

CODO ERGO SUM

bfoster
Starting Member

30 Posts

Posted - 2008-03-27 : 14:44:21

Make changes to match your table definition as appropriate, but it means you need to do something like this on the INSERT INTO line.

SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview ON

INSERT INTO dbo.Cms_MenuItems_Preview(Id, [Name], Url, Parent, IsActive, SorIndex)
SELECT Id, [Name], Url, Parent, IsActive, SortIndex
FROM dbo.Cms_MenuItems

SET IDENTITY_INSERT dbo.Cms_MenuItems_Preview OFF

Linok
Starting Member

34 Posts

Posted - 2008-03-27 : 15:30:53

duh :-P

It was one of those things where I stared at it for so long, I was missing the obvious.

Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources