[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-07-15
Medium Priority
Last Modified: 2014-07-15
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.

Question by:fskilnik
  • 2
  • 2
LVL 27

Accepted Solution

Shaun Kline earned 2000 total points
ID: 40196863
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.

Author Comment

ID: 40196928
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!
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 2000 total points
ID: 40196941
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

Author Closing Comment

ID: 40197342
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


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question