0

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'.

Tommy
  • 83
  • 3
  • 10
  • So you want all records in F0001.dbo.Actor to be updated with the data from FDummy.dbo.Actor, if it exists, otherwise insert the data from FDummy.dbo.Acor? – Bernd Linde Nov 20 '14 at 09:36
  • If it is just the names being overwritten, (So CustomerIDs should still be the same in backup and live system?) why not simply generate update statements from a select on your backup? Sth. like `SELECT 'UPDATE Customer SET Name = ' + Name + ' WHERE CustomerID = ' + CAST(CustomerID AS NVARCHAR(MAX)) FROM Customers WHERE ID IN (range of ids where name is overwritten)` – DrCopyPaste Nov 20 '14 at 09:42
  • Yes i want all the information updated from Fdummy to f0001, as i think that would be easier to do then just the names? or i might be delusional there. – Tommy Nov 20 '14 at 09:53
  • 1
    Once this is solved, consider making backups. Or get a job ab McDonalds serving bugrders. Backups are not hard and not expensive and generally people who do not do them after an accident like this should not work in IT. One time is ok (well, not really - but people rarely seem to learn without this) but more than once - ouch. – TomTom Nov 20 '14 at 10:10

3 Answers3

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.

Community
  • 1
  • 1
mrgenco
  • 348
  • 2
  • 9
  • 27
  • I hope that you ensured that there where no new/extra records in your `[F0001].[dbo].[Actor]` table before you ran a `TRUNCATE TABLE` on it. – Bernd Linde Nov 20 '14 at 10:06
  • @Tommy This probably only worked, because the database model is really simple, if there were any rows in other tables referencing those that you are about to truncate, this will fail. – DrCopyPaste Nov 20 '14 at 12:39
  • I think the word "backup" is enough to understand that these two tables have same structure. – mrgenco Nov 20 '14 at 12:45
  • 1
    Both table having the same structure still allows for foreign key constraints being present (which OP did not mention anywhere, so just assuming there are none could lead to some issues..) that would be violated when truncating the table. Sure, in this case it worked, I just pointed out where it won't. – DrCopyPaste Nov 20 '14 at 12:51
  • I got your point now. Thank you for this warning. @DrCopyPaste – mrgenco Nov 20 '14 at 12:55
  • @DrCopyPaste hmm i guess so, but it is referenced to other tables(its a huge database,22gb, working towards a program called Visma Business) but apparently it worked this time, not had any issues with the database after this. – Tommy Nov 24 '14 at 14:46
0

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
Deep
  • 3,162
  • 1
  • 12
  • 21
  • Both tables are identical in the set up, it's just the usernames that are different which i want to replace – Tommy Nov 20 '14 at 09:56
0

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

Bernd Linde
  • 2,098
  • 2
  • 16
  • 22