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 |
|
rohcky
Starting Member
38 Posts |
 Posted - 2005-06-16 : 14:42:23
|
| I have a DTS that does updates to a table. But first it needs to check the table to make sure that the new data is distinct and not repeated data. So I set up a delete sql task that declares 2 cursors, 1 for the table I am updating (store table) and 1 for the tables I'm drawing data from (data table). The algorithm is a nested loop. Each record in the store table is compared to the all records in the data table until they match. Then it breaks the inner loop to fetch the next record from the store table.When I run the dts, it starts working, does what I want, but then stops and says that it ran out of virtual space or something similar to that. (the table is currently holding 30000 records).Does anyone know a different approach to doing this repeat check or perhaps a way to make more room to scan all records? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-16 : 14:44:21
|
| Put your input data into a staging table whose DDL is the same as your destination table. Then use T-SQL to move the new rows only:INSERT INTO DestTable(Column1, ...)SELECT Column1, ...FROM StagingTableWHERE NOT EXISTS (SELECT * FROM DestTable)Tara |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-16 : 15:01:25
|
| Agreeing with Tara. DTS is delicate and can break easily. I usually DTS to a table of VARCHAR columns, and do the conversion to the proper datatype when the data is moved to the target table.Using this technique, it's possible to review the source data when errors occur.Sam |
|
|
|
rohcky
Starting Member
38 Posts |
Posted - 2005-06-16 : 15:39:50
|
| Thank you for the input. Makes more sense than deleting just to re-insert.**update**I can't do the INSERT INTO ...()SELECT ...,...FROM ...WHERE NOT EXISTS(SELECT ...)because im using an aggregate function to populate one of the fields.Here is my statement if it might help:INSERT INTO tblDestination (ID, Item, ItemValue)SELECT b.ID, c.ItemName, SUM(a.Cost * b.Quantity * b.Difficulty)FROM tblCostA a INNER JOIN tblCostB b ON a.CostID = b.CostID INNER JOIN tblItem d ON b.ItemID = d.ItemID INNER JOIN tblItemName c ON d.ItemNameID = c.ItemNameIDGROUP BY b.ID, c.ItemNameI know everything looks convoluted, but that's the way the DB was set up so that's how I have to do things. Any help would be appreciated.**end update** |
|
|
|
tomino79
Starting Member
13 Posts |
Posted - 2005-06-17 : 08:49:18
|
| I recommend you to insert the data, even duplicties, and the to call a stored procedure to delete duplicities.- or -Insert the data to a temp tableThen call a stored procedure to pump the data from temp do destination, using update and the insert. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-17 : 11:17:35
|
quote:
Originally posted by tomino79
I recommend you to insert the data, even duplicties, and the to call a stored procedure to delete duplicities.
That is just unnecessary. Why add this type of overhead to something so simple?quote:
Originally posted by tomino79
- or -Insert the data to a temp tableThen call a stored procedure to pump the data from temp do destination, using update and the insert.
Well the suggesting about a staging table is sort of a temp table anyway. But why would you want to update and insert when one insert statement will do?Tara |
|
|
|
|
|
|
|
|
Delete Algorithm Help