Post reply

how to insert a record from one db to another db

  • August 4, 2011 at 1:27 am

    #241694

    Dear All,

    Actually one record has been deleted in customers table in db1 and it's found in db2.

    I want to insert that record from db2 to db1 (Customers table ), while running the below query Iam facing the error.

    insert into db1.dbo.Customers select * from db2.dbo.Customers where vuserid='us62222'

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

    I have tried to off the IDENTITY_INSERT property on both tables on both dbs..but no use.

    Please advise.

    Thanks and Regards,

    Ravi.

  • August 4, 2011 at 4:02 am

    #1363519

    You have to set identity_insert(Customers) ON and then insert the values.

    Look up SET IDENTITY_INSERT on BOL.

    -- Gianluca Sartori

  • August 4, 2011 at 5:11 am

    #1363542

    And you would only set it ON for the table you are inserting into, not the one you're reading from. And remember to set it to OFF when you're done.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 4, 2011 at 5:14 am

    #1363545

    You can use identity insert ON if you want an exact replica of the row from the second db , however if you want to insert just the data without the need for an exact value from the identity column then simply replace the * in select statement with all column expect the identity column.

    Jayanth Kurup[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply