Add / Delete - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Add / Delete

Author

Topic

slboytoy
Starting Member

30 Posts

Posted - 2004-12-02 : 10:44:09

I have this in a DTS package that checks if the record has to be added, or delete (A or D in the first column).
How can I make it, so if it tried to add, it doesn't make duplicate records?

And, is there a way to return the results. ie..
A - 10 records
D - 2 records
else - 2 records skiped
total - 14 records?

That when, When I go read the history of the job, it will show up??



Function Main()
	Select Case Left(DTSSource("Col001"), 1)
		Case "A"
			DTSDestination("CustomerNumber") = DTSSource("Col005")
			DTSDestination("AlternatePartNumber") = DTSSource("Col004")
			DTSDestination("PartNumber") = DTSSource("Col002")
			DTSDestination("DivisionCode") = DTSSource("Col003")
			Main = DTSTransformstat_InsertQuery
		Case "D"
			DTSDestination("CustomerNumber") = DTSSource("Col005")
			DTSDestination("AlternatePartNumber") = DTSSource("Col004")
			DTSDestination("PartNumber") = DTSSource("Col002")
			DTSDestination("DivisionCode") = DTSSource("Col003")
			Main = DTSTransformstat_DeleteQuery
		Case ELSE
			Main = DTSTransformStat_SkipRow
	End Select       
End Function

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-02 : 12:00:25

Sorry, I wouldn't do it that way. I would bcp the data in to stage and the use tsql to populate the destination table.

What do you consider a duplicate to be?

Brett

8-)

slboytoy
Starting Member

30 Posts

Posted - 2004-12-02 : 12:49:11

Duplicate would be 'all 4 columns, (there are only 4 columns in my table) are the same, as what you are trying to insert.)

So bulk copy all of my adds and deletes text file, into a stage table. Then when I populate the destination table, won't I be doing the same thing as I am doing in the first post? Checking first column for an 'A' of 'D' then doing what is in the case statement? Could you explain how you would use 'tsql'?

My Destination table is about 2 million records, with my add/delete text file being about 100 records a day..

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 14:16:45

Batch processing in T-SQL is orders of magnitude more efficient than row by row processing in VBScript.
Plus you have much more control over your data, and can easily manipulate it any way you want.

If you need to keep track of the load history, you can log the results of the processing in a log table.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources