Overwriting records from a temporary table

Hi,

My company is small and cannot afford a database expert so please keep it as simple as possible!

I've exported a sample data set of 10000 from a table containing address records from SQL 2000 to Access. This address data was badly formatted and I used address cleaning software against the Access database to clean up the fields. I then imported the sample results back into the SQL database as a temporary table.

What is the easiest way for me then to update the main address Table in SQL with the cleaned 10000 sample records without affecting the other records within that Table?

The main address Table has many dependencies in SQL so I don't want to affect anything here either.

Hope you experts come to my rescue again!

mgharbourneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
If it has a primary key than it is simple:

Update YourTableName
set Col1 = t.Col1,
     Col2 = t.Col2,
     Col3 = t.Col3,
    ...
From  YourTableName
          Inner Join TempTable t On YourTableName.PrimaryKey = t.PrimaryKey

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mgharbourneAuthor Commented:
Hi,

Many thanks for your reply. I tried following the Update method as shown above and the Syntax check was successful after mapping all of the columns (51 of them!), but I'm receiving the following error when I try to run this against a test database:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'xyz'. Cannot insert duplicate key in object MyAddressTable.
The statement has been terminated.

I thought I had correctly identified the Primary Key within the Table. Any suggestions what else I can try?

k_murli_krishnaCommented:
Make sure you identified the primary key correctly. Fire following queries to confirm:
select count(*) from myschema.mytable;
select count(*) from myschema.mytable group by <comma separated PK columns>;
Both results should be same. Then fire similar query with group by indiviual PK identified columns one at a time. If any of the results match with overall count(*), then that column itself is unique.
Do not update any of the primary key columns while preparing & firing update statement as given by acperkins. Now you should get through. All the best.
mgharbourneAuthor Commented:
Guys,

Many thanks for your help.

You've made a DB novice very happy.


Anthony PerkinsCommented:
You are welcome :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.