Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mshsilver
Posting Yak Master
112 Posts |
 Posted - 2006-12-31 : 05:46:51
|
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
3279 Posts |
Posted - 2006-12-31 : 06:07:39
|
| Import them into a "staging" table.Then manually INSERT or UPDATE your contact table from this staging table.rockmoose |
 |
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2006-12-31 : 07:00:06
|
| 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. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-31 : 07:10:20
|
| Standard procedure is :First update records that have changedThen insert any new recordsAlong the lines of:1. update contact set .... from contact inner join stage on contact.firstname = stage.firstname and contact.lastname = stage.lastname2. 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 |
|
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2006-12-31 : 08:40:44
|
| 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? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-31 : 09:02:39
|
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 |
|
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2006-12-31 : 15:24:53
|
| 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. |
|
|
|
|
|
|
|
|
Updating records without uniqueid as a ref help!