Mass update of records (SQL Svr 2k) [RESOLVED]

[tailz]

Hi all,

In one hand I have a table with approx 200,000 records in and a field that needs populating.

In the other hand I have a text file containing 200,000 identifying fields and the value that should be in the field delimited by a tab.

Whats the fastest way to do the update?

I have bad hangover and everything blurry so hope that makes sense :]

Cheers

[Blade]

If they are all new rows, use the Import wizard in Enterprise Manager to import straight into the table.

If they are existing rows that just need updating you can import them via above method into a temp table then do a sql update from query analyser.

Could probably help a bit more if I knew what was in the text file and the data structure of the table you are trying to put them into.

Blade

[szlamany]

Is this a regularly occurring requirement - or a one time need?

BULK INSERT will easily take you text file and create a table.

UPDATE ... FROM ... will easily update an existing table, JOINING the new table data for a source of information.

Give us some table names and field layouts so we can see what you need.

Another way we do stuff like this all the time (we have to sync mainframe and SQL data nightly at some customers sites) is we have a little VB program that creates a .SQL scripts of UPDATE, INSERT and DELETE statements.

That will run very slowly for 200,000 entries - but will also work.

[tailz]

They be existing rows.

Main table has an identifier of varchar(16) and the field that needs updating is varchar(100).

In the text file, the first field is the identifier and second field is the value to import.

Can you give me approx example of the sql update you would do cause thats the bit I cant figure out

thanks for your help

[szlamany]

Insert into OldTable Select Col1,Col2 From NewTable
Where Col1 not Exists (Select * From OldTable Where OldTable.Col1=NewTable.Col1)

I typed that free-hand - don't have SQL here on this PC.

Update OldTable Set Col2=NewTable.Col2
   From OldTable Left Join NewTable on NewTable.Col1=OldTable.Col1

That should do the update - or something very close to that!

[tailz]

ok thats great, thanks