Bulk Update
-
SQL Server 2000.
What are the basic steps to updating a table based on an outside source? For instance, I do an initial import of the outside source data, now I must perform routine updates based on the changes. I know how to update a single field, however, I do not know the proper way to update every field.
Any help would be greatly appreciated!
-
I understand that I can use one SET as follows:
UPDATE TABLE1
SET FIELD1 = 'ABC',
FIELD2 = '123'
WHERE ID = '555';
The question I still have is that my values are dynamic based on my outside source data. Furthermore, in my tables, two fields make up the key I would use to determine matching records such as:
UPDATE TABLE1
SET FIELD1 = 'ABC',
FIELD2 = '123'
WHERE ID = '555' and CITY = 'LA'
My outside source is another SQL Server.
Again, any help is appreciated
-
Not sure if I have the rioght end of the stick here, but I presume you are looking ofr a query that allows you to update based on a join?
something like
UPDATE TABLE1
SET FIELD1 = 'ABC',
FIELD2 = '123'
FROM TABLE1 t1
INNER JOIN LinkedServer.Database.Owner.Table2 t1
ON t1.ID = T2.ID
WHERE ID = '555' and CITY = 'LA'
Hopefully that is what you're after, otherwise you might like to try to explain again
-
Thanks, Steve. I think I was able to come up with something that will work, but I don't know if it's the right way to do it.
Update TABLE1
set
FIELD1 =
a.FIELD1 from OUTSIDETABLE a inner join
TABLE1 b on a.KEY1 = b.KEY1 and
a.KEY2 = b.KEY2,
FIELD2 =
a.FIELD2 from OUTSIDETABLE a inner join
TABLE1 b on a.KEY1 = b.KEY1 and
a.KEY2 = b.KEY2
I will ultimately be updating roughly a dozen fields with a table that will have an initial size of 50K but grow to 200K. Whatever I use, it must be able to handle these numbers fairly quickly, or as fast as possible.
Thanks again, and I like your slogan!
-
Juan use the UPDATE FROM as Steve posted it but without the where clause
UPDATE t1
SET FIELD1 = t2.FIELD1,
FIELD2 = t2.FIELD2
FROM table1 t1 JOIN LinkedServer.Database.Owner.Table2 t2
ON t1.Key1 = t2.key1 and t1.Key2 = t2.key2
Cheers,
* Noel -
Excellent, that makes perfect sense! Thank you!
-
For cases where I don't really care what columns have changed, only care that the row has changed, I use checksums with the load and base tables to identify changed rows. For this to work both the load table and the base table must have the same unique index.
1) Generate checksums for the base table rows and the load table rows.
2) Delete load table rows where the checksums match the base table rows.
3) Delete base table rows where corresponding rows reside in the load table.
4) Insert load table rows into the base table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply