SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Updating records without uniqueid as a ref help!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mshsilver
Posting Yak Master

112 Posts

Posted - 12/31/2006 :  05:46:51  Show Profile
Hi, I have a mySQL DB with a 107,000 contact records which i have now converted to SQL Express for performance reasons. When i imported to Express the uniqueid's got changed to new values which is fine. The problem i now have is that people were still inputting and updating records for a few weeks on the mySQL DB these records i can export from mySQL into CSV format using a date reference which leaves me 900 ish contacts that need updating in the new SQL Express DB, to get it up-to-date.

My problem is how to update the data from CSV to SQL Express with out a uniqueid as a reference?

Thanks for your help

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/31/2006 :  06:07:39  Show Profile
Import them into a "staging" table.
Then manually INSERT or UPDATE your contact table from this staging table.


rockmoose
Go to Top of Page

mshsilver
Posting Yak Master

112 Posts

Posted - 12/31/2006 :  07:00:06  Show Profile
That is a good idea, thank you.

Is there anyway to update/insert the data from the "staging table" to the "contact table" where staging.firstname = contact.firstname and staging.lastname = contact.lastname etc(to match records seeing as there is no uniqueid reference). So if it doesn't find a record it inserts it in a new row and if it does find a record it updates all the fields?

Unfortunately i only know the basic insert / update queries and this is out of my comfort zone :) I have 2 days to get this data across to impress my new boss! so i am open to any help or useful links anyone has to help me learn quick.

Thanks again.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/31/2006 :  07:10:20  Show Profile
Standard procedure is :

First update records that have changed
Then insert any new records

Along the lines of:

1. update contact set .... from contact inner join stage on contact.firstname = stage.firstname and contact.lastname = stage.lastname
2. insert contact(...) select ... from stage where not exists(select * from contact where contact.firstname = stage.firstname and contact.lastname = stage.lastname)

> "Unfortunately i only know the basic insert / update queries and this is out of my comfort zone"
This is pretty basic stuff, and you should be able to handle it.

Set up some test scenario where you can test things out first, if you are uncomfortable/uncertain.


As always, posting table structure and sample data here will help us help you.

rockmoose
Go to Top of Page

mshsilver
Posting Yak Master

112 Posts

Posted - 12/31/2006 :  08:40:44  Show Profile
Your being a great help, thanks a lot.

When i was asked to take over this project from a colleague who i think has messed somthing up!

I was about to copy the fields with data to give you an example of the csv file and the sql express fields. Having gone through them i now see there are many fields in the express db that are not in the csv file :) i am going to login to the client server and export the csv file again to see if he lost data some how. The fields should all be exactly the same. The only difference being the data inside.

i will figure out what has gone wrong then post the data, maybe you could point me in the right direction, when you see what i am looking at.

Really appreciate your help.

Can i attach files in the forum or do i have to just copoy and pasty data?
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/31/2006 :  09:02:39  Show Profile
Ok, at least someone seems to be -working- new years eve

> "Can i attach files in the forum or do i have to just copoy and pasty data?"
Just copy and paste, you can't attach files.
To keep any formatting enclose in [ code][ /code] tags. (no space, I just put here it to show)

rockmoose
Go to Top of Page

mshsilver
Posting Yak Master

112 Posts

Posted - 12/31/2006 :  15:24:53  Show Profile
Yep, working away on new years eve :) You must be as mad as me. I am downloading the correct CSV file from the clients server now should have it in a little while. I will probably upload some data here tomorrow afternoon to give you a better idea of what i am trying to do.

Anyway, Happy New Year to you. and all else that read this post.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000