Copying data from one table to another (same SQL Server 2008 database)

Hi there!

I had to change a certain field definition (nvarchar(3000) to nvarchar(max)) but I could not do it in the table (say tableName1) "to itself". Therefore I created a second table (say tableName2), identical to the first one but this field defined in the new way.

When I try to:

insert into dbo.tableName2
select * from dbo.tableName1

I get the following error message:

"An explicit value for the identity column in table 'dbo.tableName2' can only be specified when a column list is used and IDENTITY_INSERT is ON."

What should I do to solve this issue?  Important: I want both tables to have their primary key with identical values, that is, I would like to have tableName1 data fields copied EXACTLY in the corresponding data fields of tableName2.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You should do exactly as the error message says, use the SET IDENTITY_INSERT to temporarily turn off the creation of identity values for your table, and provide each column name in your INSERT and SELECT statements.

Here is the syntax for the SET IDENTITY_INSERT command:
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Once you have completed inserting the data, re-run the SET command with OFF.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fskilnikAuthor Commented:
Hi, Shaun!

Excellent, but I did something wrong below, because I don´t know how to read this syntax... could you please have a look:

SET IDENTITY_INSERT [databaseName].[dbo.tableName2] ON

insert into dbo.tableName2 (QuestDBID, MaterialID, PageNumber, etc)
select from dbo.tableName1(QuestDBID, MaterialID, PageNumber, etc)

SET IDENTITY_INSERT [databaseName].[dbo.tableName2] OFF

I guess something really small must be changed, but I don´t know what. Thanks!
Shaun KlineLead Software EngineerCommented:
Your select statement should be:

SELECT <field list>
FROM <table>
<WHERE condition, if needed>

insert into dbo.tableName2 (QuestDBID, MaterialID, PageNumber, etc)
select QuestDBID, MaterialID, PageNumber, etc
from dbo.tableName1
fskilnikAuthor Commented:
Perfect, Shaun.

Thank you for your patience and your expertise.

Have a great day,

P.S.: just for my future reference (perhaps others), it went as expected doing exactly like that:


insert into dbo.tableName2(QuestDBID, MaterialID, PageNumber, etc)
select QuestDBID, MaterialID, PageNumber, etc from dbo.tableName1

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.