Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2012-05-25 : 12:34:15
|
We have a job that transfers credit card data from one database to another but found that not all money was being transfered.I've used the left join and can see 35 missing amounts but I don't know how to insert the missing values into the right hand table easily. I could do it manually but would take a long time. What would I need to do? Here is the join: SELECT g.giftid, g.amount as [CORWEB GIFT],eg.amount as [Radiothon GIFT], g.date as [CORWEB Date], eg.date as [Radiothon Date] FROM LCRVWDB2.CORWeb_SC.dbo.CORWeb_Gift G left JOIN dbo.ExternalGift EG ON EG.DATE = G.DATE WHERE g.Date between '05/22/2012' AND '05/27/2012' AND g.GiftStatusID = 19 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2012-05-25 : 12:40:52
|
Thanks for the quick reply. I see then that i have to get just the missing rows. When i do a left join I get all the rows but how do i get just the missing rows in a select command? quote:
Originally posted by tkizer
INSERT Table1 (Column1, Column2, ...)SELECT g.giftid, g.amount as [CORWEB GIFT],eg.amount as [Radiothon GIFT], g.date as [CORWEB Date], eg.date as [Radiothon Date] FROM LCRVWDB2.CORWeb_SC.dbo.CORWeb_Gift G left JOIN dbo.ExternalGift EG ON EG.DATE = G.DATE WHERE g.Date between '05/22/2012' AND '05/27/2012' AND g.GiftStatusID = 19 Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2012-05-25 : 12:55:59
|
Do I use IF EXISTS?Also I don't know how to hardcode the 'CC' as in:INSERT INTO ExternalGift (Amount,GiftType,Date,GiftAid,StationID)VALUES(@dAmount,'CC',@dDate,@bGiftAid,@iStationID) quote:
Originally posted by tkizer
INSERT Table1 (Column1, Column2, ...)SELECT g.giftid, g.amount as [CORWEB GIFT],eg.amount as [Radiothon GIFT], g.date as [CORWEB Date], eg.date as [Radiothon Date] FROM LCRVWDB2.CORWeb_SC.dbo.CORWeb_Gift G left JOIN dbo.ExternalGift EG ON EG.DATE = G.DATE WHERE g.Date between '05/22/2012' AND '05/27/2012' AND g.GiftStatusID = 19 Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2012-05-25 : 13:18:33
|
Great thanks, that worked.quote:
Originally posted by tkizer
INSERT Table1 (Column1, Column2, ...)SELECT g.giftid, g.amount as [CORWEB GIFT],eg.amount as [Radiothon GIFT], g.date as [CORWEB Date], eg.date as [Radiothon Date], 'CC' FROM LCRVWDB2.CORWeb_SC.dbo.CORWeb_Gift G left JOIN dbo.ExternalGift EG ON EG.DATE = G.DATE WHERE g.Date between '05/22/2012' AND '05/27/2012' AND g.GiftStatusID = 19 AND EG.DATE IS NULL Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
how to use left join to insert missing values