| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2009-02-13 : 15:17:25
|
| How to code to insert records from table A to B only records not in B (I need to make OrderID unique)?I used code below. Is it ok?insert into B select * from A where OrderID not in (select OrderID from B) |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-13 : 15:21:19
|
| That will work. If it's a large amount of rows, you may see better performance by doing a left join and selecting rows where b.orderid is null. If it's a small amount of rows though, you're good.Mike"oh, that monkey is going to pay" |
 |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-02-13 : 15:28:29
|
| Mike, Can you help me to complete code usiing b.orderid is null? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-13 : 15:42:55
|
| Create table #a(orderid int not null)create table #b(orderid int not null)insert into #a values(1)insert into #a values(2)insert into #b values(1)select * from #aselect * from #binsert into b select a.OrderID from #a a left join #b b on a.orderid=b.orderid where b.orderid is nullMike"oh, that monkey is going to pay" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-13 : 15:47:40
|
quote:
Originally posted by Sun Foster
How to code to insert records from table A to B only records not in B (I need to make OrderID unique)?I used code below. Is it ok?insert into B select * from A where OrderID not in (select OrderID from B)
This won't Work. See here Why?http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-03-05 : 11:14:58
|
| I tested the way from Tara Kizer. It only works when B not empty.When B was truncate, it did not work. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2009-03-05 : 15:19:54
|
| Here is the code and tables. If you run code, 5 rows will insert into B. (should be only 3 rows)insert into B select * from A where not exists (select * from B where A.OrderID = B.OrderID)table AOrderID Name1 AAA2 BBB3 CCC1 AAA1 AAA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Insert records from A to B only records not in B