Is this possible using DTS? - 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)

Is this possible using DTS?

Author

Topic

Munchausen
Starting Member

25 Posts

Posted - 2005-07-24 : 23:50:01

Currently, we have a very slow process for adding to our database.

We loop through many incoming messages. For each message, it is parsed in a C# application, and then a SQL stored procedure is called. This stored procedure does a select to see if the record already exists. If it does, an update is performed. If not, an insert is performed.

To speed it up, I was considering writing all of the parsed messages to a text file, and then using a DTS package after the loop to add them to the database at once. I know this would work fine (at least I think it would) if I were only doing inserts. I'm unsure if I can replicate the conditional logic that checks to see if it already exists, and the following update or insert decision within DTS.

So if I do a bulk insert to a table, what will happen to new records that share primary keys with records that already exist? Will the old records be overwritten (thus acting as an update)?

Any guidance here would be greatly appreciated.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-25 : 00:09:26

an error occurs because of the PK constraint

quote:
Originally posted by Munchausen

So if I do a bulk insert to a table, what will happen to new records that share primary keys with records that already exist? Will the old records be overwritten (thus acting as an update)?

Any guidance here would be greatly appreciated.

--------------------
keeping it simple...

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-07-25 : 03:06:18

You could still encapsulate this in a DTS package by splitting the data into those that need an update and those that need an insert (using a query)

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Munchausen
Starting Member

25 Posts

Posted - 2005-07-25 : 09:45:26

Thanks for the responses. If I query each record to see if it exists, then write to a text file if it doesn't and call a SQL update if it does, will I notice any speed difference, do you think?

Munchausen
Starting Member

25 Posts

Posted - 2005-07-25 : 10:03:25

I had a thought. But I'm not sure how practical it is.

Currently, I think it's so slow because we're doing a select, followed by an insert or update thousands of times, due to the number of records we have.

So I'm wondering if this would be quicker, even though it seems more complex.

1. Write all the data to a text file.
2. Use DTS to insert all the data into a blank holding table.
3. Run a stored procedure that a) queries this table to find all records which share a primary key with the other table, b) updates the other table with the values of the query (is this even possible?), and c) removes those records which match the query.
4. Use DTS to insert the remaining records into the other table.

Would this work? Would this be faster? And, if it works, is there an even simpler solution I'm not seeing? The primary concern at this point is speed.

Thanks in advance.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-25 : 10:35:37

Hi
Yes, that's much more like it. I don't agree that it's more complex. You're usually much better of getting your information into SQL Server and processing it there if you want a performance boost.
I'd be inclined to have one stored proc that did the update:

UPDATE
	mt
SET 
	mt.Field1 = mht.Field1, 
	...
FROM
	dbo.MyTable AS mt
	JOIN dbo.MyHoldingTable AS mht
		ON mt.MyKey = mht.MyKey

and another that does the insert:

INSERT INTO
	dbo.MyTable
	(
		Field1, 
		...
	)
SELECT
	mht.Field1, 
	...
FROM
	dbo.MyHoldingTable AS mht
	LEFT JOIN dbo.MyTable AS mt
		ON mt.MyKey = mht.MyKey
WHERE
	mt.MyKey IS NULL

If you're using Bulk Insert for this, the use of DTS becomes a bit redundant.
Mark

thebruins
Starting Member

31 Posts

Posted - 2005-07-25 : 10:35:43

I'd merge step 3 and 4 into one step by creating a DDQ. In a DDQ you

create a mapping between the holding table and final table
define parameterized insert/update queries
define a lookup script that checks if a record already exists
use activex-code to do the lookup to see if you need an insert or update query and handle records accordingly

basically, a DDQ does exactly what you're describing, so I guess a DDQ is an easier solution once it's setup properly. don't know about speed though

Munchausen
Starting Member

25 Posts

Posted - 2005-07-25 : 11:37:30

Ok, found another snag to complicate things.

As it turns out, when we receive a record which exists and needs to be updated, we only receive the fields that need to be updated.

For instance, a complete record might look like this:

Claim# (PK): 12345
Amount: 555
FilerCode: 12-34567
PortCode: 345-687

If the Amount needs to be updated, this is what we receive:

Claim# (PK): 12345
Amount: 556
FilerCode: NULL
PortCode: NULL

Currently, within the stored procedure that gets called for each record, all of the values for the fields are set to DEFAULT before the update runs, which seems to preserve the original values if the new values are NULL. Would this same functionality work for the bulk update (like the update example mwjdavidson provided)?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-25 : 16:13:18

using the DEFAULT keyword specifies that the default value defined for the column is to replace the existing value. I.e. 12-34567 and 345-687 are the default values for FilerCode and PortCode respectively in your table. I would suggest using the following:

UPDATE
	mt
SET 
	mt.Field1 = COALESCE(mht.Field1, mt.Field1), 
	...
FROM
	dbo.MyTable AS mt
	JOIN dbo.MyHoldingTable AS mht
		ON mt.MyKey = mht.MyKey

This will update to the current value where a NULL has been supplied. It would appear that it's not possible to use the default keyword as a parameter passed to a function.

Mark

Munchausen
Starting Member

25 Posts

Posted - 2005-07-25 : 16:25:50

My hat's off to you, sir.

This will really help me a lot.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-25 : 17:26:58

Baron von Munchausen?

Munchausen
Starting Member

25 Posts

Posted - 2005-07-25 : 17:53:47