Problem duplicating/updating table records

[anonim1]

Hello, I have the following problem:

1. I have a MS SQL 2000 database with a table of user accounts called tbl_User.

2. A faulty script was run on the data in the table and, it deleted each user's first and last name.

3. I was able to reconstruct the data and have created another table called tbl_UserFixed with the correct data.

4. Being new to SQL, I tried to delete all of the records in tbl_User and insert the correct records from tbl_UserFixed. However, this failed due to the table having constraints/dependencies.

5. My next idea was to rename tbl_User to tbl_UserOld and then rename tbl_UserFixed to tbl_User. However, when I did this in a test database, I noticed that the dependencies were not inherited.

6. I've tried using DTS, but I was unsuccessful.

7. What is the simplest way to accomplish this task? I need to fix this ASAP!

Thank you in advance.

 

[johnwm]

You can do an Update query from tbl_UserFixed to the tbl_User. Check your field names!

UPDATE tbl_User INNER JOIN tbl_UserFixed
ON tbl_User.ID = tbl_UserFixed.ID
SET tbl_User.FirstName = tbl_UserFixed.FirstName

Repeat for LastName. Make sure your data is backed up before running ANY exciting queries!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[anonim1]

Johnwm,

Thank you, your example code worked just fine! I am new to SQL, so I knew that it could be done with an update statement, and there it is! I have definitely learned my lesson this time, I will make a duplicate database and test my custom scripts on there first! Thanks again.