Hi all - a friend of mine has run across a situation and we can't seem to find a good SQL statement to handle it. Basically, we want to update multiple records in one table with data from another table. This is what we have:
To the best of my knowledge, Table B is a "temp" table that is being created by using DTS (SQL Server) to import a CVS file. Really what we want to do is merge this data with the records in Table A. We need to find the records in which the RecordID matches from Table A to Table B and, for each of those cases, copy the FilePath from TableB.FilePath to TableA.FilePath.
Unfortunately, I'm at a bit of a loss on how to do this. Anyone have any ideas?
My suggestion, which I admit is a bit ugly, is to do something like this:
1. Do a join between Table A and Table B where RecordID matches, keeping everything from Table A except for FilePath, which we keep from Table B. Store this in a temporary table.
2. Remove all records from Table A in which the RecordID exists in the temporary table we created in step 1.
3. Do a mass insert of everything in the temporary table we created in step 1 into Table A.
4. Delete the temporary tables.
I don't really like the idea of constantly deleting large chunks of data and moving things around like this. Anyone else have any better ideas?