Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Steve2106
Posting Yak Master
183 Posts |
 Posted - 2012-07-13 : 07:24:24
|
| Hi There,I have two tables MainFile and MainFileA.I need to move some records from MainFile to the archive file MainFileA. The record numbers that need to be moved are in a .csv file and I would like to run through this file and for each recordid found move the corresponding record from MainFile to MainFileACould anyone help me with how I would do that using a query / script.Thanks for your help.Best Regards,Steve |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-13 : 07:31:32
|
Import the record numbers from the CSV file to a database table. You can do that using Import/Export wizard (right click on the database name in object explorer in SQL Server Management Studio, then Tasks -> Import Data), or you could even construct insert statements in Excel.Once you have the record numbers in a table, moving is easy enough. You can have two statements, one to insert and the other to delete:INSERT INTO MainFileA (col1, col2, ... colN)SELECT m.col1, m.col2, ... m.colNFROM MainFile m INNER JOIN RecordIDTable r on r.RecordId = m.RecordId; Then once the insert is successful, delete from the MainFile table, using the same join.You can also use OUTPUT clause to do the insertion and deletion in a single statement, if that is a consideration (i.e., it is a production table and you don't want to have same records in MainFile table and MainFileA even for a short period of time) |
 |
|
|
|
|
|
|
|
Moving records