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

AHWB

Ten Centuries

Points: 1096
More actions
January 21, 2013 at 10:24 am

#393984

This should be nice and simple, but I cannot work out how to do this, hence help required.
(Table names changes to simplify my query)
I have a table called T1 and a second table called T2. I want to copy all rows from T2 to T1 except where the data exists using a nominated column
For example if I have T1 that contains a list of names useing these columns, (id, name, number, etc) and T2 contains (id, name, number etc) I want to insert all the entries from T2 into T1 however if the name in T2 already exists in T1 don’t insert.
So in this example only Rod would be inserted from T2 to T1 As Ashley already exists within the table NAMES
T1
ID, Name, Number....
1, Ashley, 12345
2, Bob, 54321
3, Tom, 12345
T2
ID, Names, Number.....
1, Ashley, NULL
2, Rod, NULL
Expected outcome
T1
ID, Name, Number....
1, Ashley, 12345
2, Bob, 54321
3, Tom, 12345
4, Rod, NULL

Viewing 5 posts - 1 through 4 (of 4 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: 101098 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: 286590 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/[/url]

_______________________________________________________________

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/