| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-11-05 : 13:43:55
|
| I have an DTS to which dumps data from server1 database1 table1 to server2 datbase 2 table 2.the only problem here is when I insert records I don't want to insert the records which already is present in the destination.IS there any way to do this |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 15:37:04
|
| Use T-SQL commands for this instead of a DTS task. So use EXISTS in your command.Tara |
 |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-11-05 : 16:23:23
|
| we should work around without linked server...In DTS source query we say select emp_id ,emp_name,emp_aage from emp_detail and now can we write any vbscript so that when it tries to insert check for the duplicate emp_id..if its there then ship that row..Is that possible |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 16:31:28
|
| Why not use a linked server:INSERT INTO Server2.DB2.dbo.Table2SELECT * FROM Server1.DB1.dbo.Table1WHERE NOT EXISTS (SELECT * FROM Server2.DB2.dbo.Table2)Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-11-05 : 17:02:54
|
| No they don't allow because of the performance issue.....So I have to work around Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 17:06:05
|
| What performance issue? How is this going to be slower than DTS? It won't be!Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-05 : 17:27:26
|
| Correlated subqueries and joins between linked servers are SLOWER THAN MOLASSES. Think of an MS Access query joining two linked tables...yes, THAT SLOW or SLOWER. The mechanism to do the joins over the linked server is approximately the same as what Access would do (cursor)Better to just DTS or SELECT the entire table from the linked server into a local table and do the comparison there. I have other ideas that might work but I need more info on the table structure, number of rows, and how the comparison will be done. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 17:31:37
|
| Never used Access...So then use DTS to move the entire table over to a staging table. Then:INSERT INTO TableASELECT * FROM StageTableWHERE NOT EXISTS (SELECT * FROM TableA)Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-11-06 : 01:12:59
|
| No they don't allow because of the performance issue.....So I have to work around Tara |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-11-06 : 03:01:30
|
| You couldcreate unique index mytable (col1, col2, col3) WITH IGNORE_DUPS(or syntax close to that)then just insert every row from the remote server. Rows that already exist won't be inserted.Duplicate records will generate warnings but I think DTS would ignore them. Not sure how unique index with ignore dups would perform compared to a staging table and NOT EXISTS.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 12:17:31
|
quote:
Originally posted by sqllearner
No they don't allow because of the performance issue.....So I have to work around Tara
What part don't they allow in my last post?Tara |
|
|
|
|
avoiding duplicates