avoiding duplicates

[SqlHunter]

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

 

[JohnDTampaBay]

The source query should look something like this. You will need to provide the appropriate key columns from the EXISTS subquery and Server2 will also need to be a linked server on Server1. Good luck!

SELECT * FROM server1.database1.dbo.table1 AS t1
WHERE NOT EXISTS(SELECT *
                FROM server2.datbase2.table2 AS t1
                WHERE t1.KeyColumn1 = t2.KeyColumn1
                AND t1.KeyColumn2 = t2.KeyColumn2)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SqlHunter]

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

 

[gradley]

SqlHunter when you are dumping the data from one connection to the other, are you using an Execute SQL task, Transform Data Task or Data Driven query? If you are using the 2nd or 3rd option, try using a "lookup" to see of the record exists first.

If using the Transform Data Task and if the record exists (using Lookup), set Main = DTSTransformStat_SkipRow instead of Main = DTSTransformStat_OK.

If using Data Driven Query and if the records exists (using lookup), set Main = DTSTransformstat_UserQuery and don't perform a command in UserQuery. If record does not exist, set Main = DTSTransformstat_InsertQuery.

I hope this helps!

 

[SqlHunter]

Iam trying out as per advice above,here I have a problem.......


This is the Vbscript I have and its going on in a continuous loop......

My DTSlookups is like this

select file_id from tbl_file_detail where file_id=?

Here is the VB Script that I have used.Still its giving problems


Function Main()

Dim get_id

get_id = DTSLookups("avoid_duplicates").Execute(DTSSource("file_id").value)


IF ISEMPTY(get_id) THEN

DTSDestination("file_id") = DTSSource("file_id")
DTSDestination("emp_number") = DTSSource("emp_number")
DTSDestination("file_type") = DTSSource("file_type")
DTSDestination("mime_type") = DTSSource("mime_type")
Main = DTSTransformStat_OK
ELSE
Main =DTSTransformStat_SkipRow

END IF


End Function

 

[MDXer]

you can dump all imorted records into a staging table and then use a select distinct with a left outer join to insert only new records.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SqlHunter]

The funny thing is If I comment out DTSDestination("mime_type") = DTSSource("mime_type") then it works..I don't know whats happening

 

[SqlHunter]

another funny thing is its not just if I comment "mime_type"...its allowing only 3 fields to get saved...if I comment any one it works....otherwise its running forever....

 

[gradley]

Try checking the "Destination" tab to see that you are specifying more than 3 fields. Also verify the Source and Destination columns tabs to make sure you are selecting all the fields you want.

When you say it is looping, what do you mean by that?

Have you tried putting the VBScript in debug and stepping through the code.

To put in debug, add the word STOP somewhere at the top of the VBScript code. Also make sure in the Properties dialog for "Data Transformation Services" that you have "Turn on Just-in-time debuggin" checked.

Just a warning on this; If you make changes, save them first before going into debug. If you are in debug and decide to exit before the script completes normally, it takes you right out of SQL Server.

OUCH! I've done that too many times.