Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table
.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list> )
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com