Updating SQL databse 1

[oalex]

Need help:What is the best way to update SQL (V7) database?<br>
Receive about 500,000 records every week - need to check if ea record already exist. If it does, need to check which column(s) needs to be updated, otherwise add record to database<br>
is there a simple solution?<br>
<br>
Does a cursor work? scan import database. check every record for existance and either update or add.

 

[MapMan]

I do the same thing. What I've done is separated the process into two phases. First I update data that exists. Then I add data that doesn't.<br>
<br>
Here's the update...<br>
<br>
CREATE PROCEDURE usp_UpdateCompany AS<br>
<br>
<br>
UPDATE CommonTables.dbo.Company<br>
SET<br>
Compname = u.Compname ,<br>
[Date Entered] = u.[Date Entered]<br>
<br>
FROM Updates.dbo.Company U, CommonTables.dbo.Company C<br>
WHERE U.CompanyID = C.CompanyID<br>
<br>
Here's the insert...<br>
<br>
CREATE PROCEDURE usp_InsertCompany AS<br>
<br>
INSERT INTO CommonTables.dbo.Company<br>
<br>
SELECT * <br>
<br>
FROM Updates.dbo.Company U<br>
Where CompanyID not in(<br>
Select CompanyID from Company C<br>
Where U.CompanyID = C.CompanyID)<br>
<br>
HTH,<br>
<br>
MapMan

 

[nitram]

Perhaps slightly off topic, but if your goal was to synchronise the data in your database with the records that you receive every week then this can be done with an UPDATE and INSERT (as explained above) followed by a DELETE.<br>
<br>
The delete removes any records which no longer exist in the weekly import.<br>
<br>
I use exactly this in a stored procedure which keeps track of files stored in a directory. <br>
<br>
Regards,<br>
<br>
Martin <p>Martin Ramsay<br><a href=mailto:Martin.Ramsay@company-net.co.uk>Martin.Ramsay@company-net.co.uk</a><br><a href=

http://www.company-net.co.uk>My

employer</a><br>Looking for interesting work in Canada - any offers ?