append only unique records

Question

append only unique records

rwyatt292

SSC Veteran

Points: 246
More actions
October 1, 2010 at 2:03 pm

#239092

I'm trying to perform a simple insert into an existing table Ship_b and I only want records from table Ship_a that have different Load_# from records that are already in table Ship_b. Looks like my where statement should get rid of any of the records from Ship_a that are the same as the records in Ship_b.
INSERT INTO [Ship_b]
([Load_id]
,[Ship_id]
,[Billing address])
SELECT ([Load_id]
,[Ship_id])
,[Billing address]
FROM [Ship_a]
WHERE [Ship_b].[Load_id] <> [Ship_a].[Load_id]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

May I direct your attention to the Merge T-SQL statement, which appears to be the method you would choose to do what you want to do.

http://technet.microsoft.com/en-us/library/bb510625.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

rwyatt292 (10/1/2010)
I'm trying to perform a simple insert into an existing table Ship_b and I only want records from table Ship_a that have different Load_# from records that are already in table Ship_b. Looks like my where statement should get rid of any of the records from Ship_a that are the same as the records in Ship_b.

How about something like...

INSERT

INTO [Ship_b]

([Load_id]

,[Ship_id]

,[Billing address])

SELECT ([Load_id]

,[Ship_id])

,[Billing address]

FROM [Ship_a]

WHERE [Ship_a].[Load_id] not in (select X.[Load_id]

from [Ship_b] X

where [Ship_a].[Load_id] = X.[Load_id])

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 3

bitbucket-25253 (10/1/2010)
May I direct your attention to the Merge T-SQL statement, which appears to be the method you would choose to do what you want to do.
http://technet.microsoft.com/en-us/library/bb510625.aspx

I'll have to look into using MERGE - thanks! 😎

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

rwyatt292 SSC Veteran Points: 246 More actions · Answer 4

rwyatt292

SSC Veteran

Points: 246

October 3, 2010 at 11:54 am

#1230781

thanks for the helpful info

rwyatt292 SSC Veteran Points: 246 More actions · Answer 5

rwyatt292

SSC Veteran

Points: 246

October 3, 2010 at 11:54 am

#1230782

thanks for the helpful info

rwyatt292 SSC Veteran Points: 246 More actions · Answer 6

rwyatt292

SSC Veteran

Points: 246

October 3, 2010 at 11:55 am

#1230783

Paul thanks for the help

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

rwyatt292 (10/3/2010)
Paul thanks for the help

Glad to help.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.