Deleting Records on Large Table

[opo]

I have a table that contains over 6,000,000 records.
It has a primary index on 4 char columns. I receieve duplicate or updated information regularly, so i need to either delete the existing record and insert the new, or update the old with new info.
I tried doing this in vb program, but the timeout expires even when set to 0. Tried a stored procedure that was run by a DTS and still no success.
Is the table just too large?
Please help.

 

[tlbroadbent]

SQL server can handle 6 million rows in a table quite easily if the process is optimized. You don't provide much information to base any recommendations on.

How do you receive the updates? Do they come in flat file? Are they in table? In another database?

How many rows of updated info do you receive? Do you get a subset of the existing table or a complete set? Can you simply replace the old data with the new?

Please prvide a little more detail about the proces. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[opo]

The updates come in a flat file. I use a DTS to put them in a temp table on the same database. I then use another DTS to try to insert them into the primary table. When the DTS fails, because of the violation of the primary key, the VB program tries to inserts the records one at a time from the temp table. When it experiences a violation, I need it to delete the old and insert the new. In the VB program the delete statement timesout. Tried to do it in a stored procedure started from the program, but it too doesn't work. The information is usually a subset of the existing table.

 

[tlbroadbent]

I would do the following.

1) Continue to import flat file to temporary table.
2) Write a SQL script or stored procedure to update matching rows and insert new rows.
3) Run both steps from a SQL Agent Job which is scheuled or run on demand.

Script to update matching rows and insert new rows:

Update MainTable Set
Col1=t.Col1, Col2=t.Col2, Col3=t.Col3, ... ColN=t.ColN
From MainTable m
Join TempTable t
On m.Key1=t.Key1
And m.Key2=t.Key2
And m.Key3=t.Key3
And m.Key4=t.Key4

Insert MainTable (Col1, Col2, Col3, ... ColN)
Select Col1, Col2, Col3, ... ColN
From TempTable t
Where Not Exists
(Select * From MainTable
Where Key1=t.Key1
And Key2=t.Key2
And Key3=t.Key3
And Key4=t.Key4)

--You may want to Truncate the temp table after the update completes or wait until the update is verified. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[opo]

It worked perfectly!
Thank you, thank you thank you!