Trying to copy one table from another database to another in SQL Server 2008 R2

Question

i am trying to copy table information from a backup dummy database to our live sql database(as an accident happened in our program, Visma Business, where someone managed to overwrite 1300 customer names) but i am having a hard time figuring out the perfect code for this, i've looked around and yes there are several similar problems, but i just can't get this to work even though i've tried different solutions.

Here is the simple code i used last time, in theory all i need is the equivilant of mysqls On Duplicate, which would be MERGE on SQL server? I just didn't quite know what to write to get that merge to work.

INSERT [F0001].[dbo].[Actor]
SELECT * FROM [FDummy].[dbo].[Actor]

The error message i get with this is: Violation of PRIMARY KEY constraint 'PK__Actor'. Cannot insert duplicate key in object 'dbo.Actor'.

Answer 1

What error message says is simply "You cant add same value if an attribute has PK constraint". If you already have all the information in your backup table what you should do is TRUNCATE TABLE which removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.

After that step you should follow this answer . Or alternatively i recommend a tool called Kettle which is open source and easy to use for these kinds of data movements. That will save you a lot of work.

Answer 2

The MERGE statement will be possibly the best for you here, unless the primary key of the Actor table is reused after a previous record is deleted, so not autoincremented and say record with id 13 on F0001.dbo.Actor is not the same "actor" information as on FDummy.dbo.Actor

To use the statement with your code, it will look something like this:

begin transaction

  merge [F0001].[dbo].[Actor] as t  -- the destination
  using [FDummy].[dbo].[Actor] as s -- the source
     on (t.[PRIMARYKEY] = s.[PRIMARYKEY]) -- update with your primary keys
   when matched then
          update set t.columnname1 = s.columnname1,
                     t.columnname2 = s.columnname2,
                     t.columnname3 = s.columnname3
                     -- repeat for all your columns that you want to update
  output $action,
         Inserted.*,
         Deleted.*;

rollback transaction -- change to commit after testing

Further reading can be done at the sources below:
MERGE (Transact-SQL)
Inserting, Updating, and Deleting Data by Using MERGE
Using MERGE in SQL Server to insert, update and delete at the same time

Answer 3

Here are thing which can be the reason :

1. You have multiple row in [FDummy].[dbo].[Actor] with same data in a column which is going to be inserted in primary key column of [F0001].[dbo].[Actor].
2. You have existing rows in [FDummy].[dbo].[Actor] with some value x in primary key column and there is/are row(s) in [F0001].[dbo].[Actor] with same value x in the column which is going to be inserted in primary key column.

List item

-- to check first point. if it returns row then you have some problem
SELECT ColumnGoingToBeMappedWithPK,
       Count(*)
FROM   [FDummy].[dbo].[Actor]
GROUP  BY ColumnGoingToBeMappedWithPK
HAVING Count(*) > 1 

-- to check second point. if count is greater than 0 then you have some problem
SELECT Count(*)
FROM   [FDummy].[dbo].[Actor] a
       JOIN [F0001].[dbo].[Actor] b
         ON a.ColumnGoingToBeMappedWithPK = b.PrimaryKeyColumn