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 |
|
bobshishka
Yak Posting Veteran
72 Posts |
 Posted - 2007-09-06 : 13:19:13
|
First off, Ive been asking a lot of questions lately and I want to sincerely thank those who have been posting VERY helpful replies. I have learned much in these past few weeks.That being said I have hopefully one of my last questions before this project is complete.Im running a DTS that imports data from a flat text file and updates 6 different tables. In one of my tables, "PERSON", I only want the records to be inserted or updated if the FCN field is new, or unique.Here is my code that updates the table (without regard for uniquneess):INSERT INTO PERSON (FIRST_NAME, LAST_NAME, MIDDLE_NAME, FCN)SELECT "FIRST", "LAST", "MIDDLE", FCNFROM DATAGRAB WHERE RECORD_DATE = convert(varchar(8), getdate()-1, 112) How can I convert it to only update records with a unique FCN value? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-06 : 13:33:50
|
INSERT INTO PERSON (FIRST_NAME, LAST_NAME, MIDDLE_NAME, FCN)SELECT "FIRST", "LAST", "MIDDLE", FCNFROM DATAGRAB WHERE RECORD_DATE = convert(varchar(8), getdate()-1, 112)AND FCN NOT IN (SELECT FCN from PERSON)
Future guru in the making. |
 |
|
|
bobshishka
Yak Posting Veteran
72 Posts |
Posted - 2007-09-06 : 14:12:46
|
| Worked Great for updating the database. Of course, one question leads to another.If I am doing my initial dump of data into a blank database, with 1000's of new records that include multiple FCN numbers, how can I only update the PERSON table with 1 record of each FCN? (doesnt matter which- if it has multiples) |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-06 : 14:41:01
|
quote:
Originally posted by bobshishka
Worked Great for updating the database. Of course, one question leads to another.If I am doing my initial dump of data into a blank database, with 1000's of new records that include multiple FCN numbers, how can I only update the PERSON table with 1 record of each FCN? (doesnt matter which- if it has multiples)
You should use the DISTINCT keyword in the select.
Future guru in the making. |
|
|
|
|
|
|
|
|
Updating only new records