inserting records without making duplicate? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

inserting records without making duplicate?

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-12 : 04:32:36

hi friends,
i want to insert date to an existing records in a table but the new data contains some records already exist in the table
is there a way i can only insert the recods that are not existing
or i should disable the primary key on the existing table then i insert all then delete the duplicate records?

i was trying with this query but i couldn't run it -- syntax error

insert into oaefile_vendor (vendor_id,vendor_name)
(select VENDOR_ID,VENDOR_NAME FROM VENDOR_UCTC WHERE
VENDOR_UCTC.VENDOR_ID not in oaefile_vendor.vendor_id)

any suggestion?
thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 04:37:58

quote:
Originally posted by ann06

hi friends,
i want to insert date to an existing records in a table but the new data contains some records already exist in the table
is there a way i can only insert the recods that are not existing
or i should disable the primary key on the existing table then i insert all then delete the duplicate records?

i was trying with this query but i couldn't run it -- syntax error

insert into oaefile_vendor (vendor_id,vendor_name)
(select VENDOR_ID,VENDOR_NAME FROM VENDOR_UCTC WHERE
VENDOR_UCTC.VENDOR_ID not in oaefile_vendor.vendor_id)
any suggestion?
thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-12 : 04:42:22

quote:
Originally posted by ann06

hi friends,
i want to insert date to an existing records in a table but the new data contains some records already exist in the table
is there a way i can only insert the recods that are not existing
or i should disable the primary key on the existing table then i insert all then delete the duplicate records?

i was trying with this query but i couldn't run it -- syntax error

insert into oaefile_vendor (vendor_id,vendor_name)
(select VENDOR_ID,VENDOR_NAME FROM VENDOR_UCTC WHERE
VENDOR_UCTC.VENDOR_ID not in oaefile_vendor.vendor_id)

any suggestion?
thanks

Just delete the duplicate rows and set unique key costraint on vendor_name column

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-12 : 06:39:49

any other solution ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 06:43:41

[code]INSERT oaefile_vendor
(
vendor_id,
vendor_name
)
SELECT DISTINCT
x.VENDOR_ID,
x.VENDOR_NAME
FROM VENDOR_UCTC AS x
WHERE NOT EXISTS (SELECT * FROM oaefile_vendor AS w WHERE w.VENDOR_ID = x.VENDOR_ID)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 08:56:50

also


insert into oaefile_vendor (vendor_id,vendor_name)
select VENDOR_ID,VENDOR_NAME 
FROM VENDOR_UCTC v
LEFT JOIN  oaefile_vendor o
ON v.VENDOR_ID =o.vendor_id
WHERE o.vendor_id IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 09:17:26

Don'y you need a DISTINCT, just in case there are duplicates found in VENDOR_UCTC?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 10:19:21

quote:
Originally posted by Peso

Don'y you need a DISTINCT, just in case there are duplicates found in VENDOR_UCTC?

E 12°55'05.63"
N 56°04'39.26"

yup if you've many to one relation you need distinct

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 11:16:40

No. If you have two records with same ID in vendor_uctc, and the Id is not present in oaefile_vendor, the insert statement will generate a duplicate key error if you omit the DISTINCT keyword.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:57:24

Oh..Ok that makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources