Import data from one table to another excluding any duplicates help

Question

Post reply

Import data from one table to another excluding any duplicates help

Viewing 4 posts - 1 through 5 (of 5 total)

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

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

Look at using the MERGE statement

http://technet.microsoft.com/en-us/library/bb510625(v=sql.100).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]

ScottPletcher SSC Guru Points: 101207 More actions · Answer 2

INSERT INTO dbo.T1 ( ... )

SELECT t2.col1, t2.col2, ...

FROM dbo.T2 t2

LEFT OUTER JOIN dbo.T1 t1 ON

t1.Name = t2.Name

WHERE

t1.Name IS NULL --only include row if not already in t1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mdsharif532 Old Hand Points: 367 More actions · Answer 3

create table #T1(ID int, Name varchar(15), Number int)

insert into #T1 values (1, 'Ashley', 12345)

insert into #T1 values (2, 'Bob', 54321)

insert into #T1 values (3, 'Tom', 12345)

Try this one..................................

create table #T2(ID int, Name varchar(15), Number int)

insert into #T2 values (1, 'Ashley', NULL)

insert into #T2 values (2, 'Rod', NULL)

SELECT T1.ID, T1.Name, MAX(Number)

FROM

(select * from #T1

UNION

select * from #T2) T1

GROUP BY T1.ID, T1.Name

drop table #T1

drop table #T2

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

ScottPletcher (1/21/2013)
INSERT INTO dbo.T1 ( ... ) SELECT t2.col1, t2.col2, ... FROM dbo.T2 t2 LEFT OUTER JOIN dbo.T1 t1 ON t1.Name = t2.Name WHERE t1.Name IS NULL --only include row if not already in t1

This approach may actually be quite a bit slower depending on the structure of the table.

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/