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 |
|
collie
Constraint Violating Yak Guru
400 Posts |
 Posted - 2007-11-21 : 09:53:42
|
| Hi,I have this scenario: I have a table one database in sql called facttable_Temp with columns CustomerName, ItemKey.The fields are varchar.I have another table called Accounts in a different database. Accounts contains fields such as CustomerName,Account. The fields are varchar.What I need to do is to check if the values for CustomerName from table FactTable_Temp exists in the field CustomerName in the table Accounts.If it exists then I need to insert the entire row for that CustomerName including ItemKey into a 3rd table called FactTable.What is the best way of accomplishing this in SSIS?Thanks Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-21 : 18:49:18
|
This query, which you could run in a task in SSIS or just straight from a stored procedure or job will do what you want. You'll need to add the other columns where I put ...I assumed ItemKey is in the Accounts table because you didn't make that clear. I added a WHERE clause to check that the CustomerNames don't already exist in FactTable, if you know for sure that they don't you could remove that.INSERT FactTableSELECT facttable_Temp.CustomerName, Accounts.ItemKey, ...FROM facttable_TempINNER JOIN Accounts ON Accounts.CustomerName = facttable_Temp.CustomerNameWHERE NOT EXISTS (SELECT * FROM FactTable WHERE FactTable.CustomerName = facttable_Temp.CustomerName) |
 |
|
|
|
|
|
loop