January 21, 2013 at 10:24 am
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
January 21, 2013 at 10:59 am
Look at using the MERGE statement
http://technet.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
January 21, 2013 at 2:18 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 21, 2013 at 3:18 pm
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
January 21, 2013 at 3:37 pm
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply