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

Posted on 2014-07-15
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
    LVL 25

    Accepted Solution

    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

    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 25

    Assisted Solution

    by:Shaun Kline
    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

    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:

    SET IDENTITY_INSERT dbo.tableName2 ON

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

    SET IDENTITY_INSERT dbo.tableName2 OFF

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now