I need to select a recordset worth of data from a table and insert that recordset into a temp table. While the data is IN that temp table, I wish to change some values so I can then copy all the data back into the original master table.
I am trying to accomplish the table data transfer using an insert statement with a subselect as such:
Set RSInsertHeaderTmp = Server.CreateObject("ADODB.Recordset")
SQLInsertHeaderTmp = "INSERT INTO rccnhdwebtmp10 (SELECT * FROM rccnhd10 WHERE (contno = ' 25432'))"
RSInsertHeaderTmp.Open SQLInsertHeaderTmp, Conn
I need to know the correct format for this statement so I may properly copy my data from one table to another. I wish to use this method because there are quite the number of columns and I don't want to insert column by column for the recordset.
Plus I want to use the temp table method because due to the nature of the program, if the data transfer is interrupted, I have a solid group of data to backtrack with.
Thank you all in advance!
Make sure you do INSERT INTO table VALUES ( select ). This requires you to SELECT in the exact same order as the columns exist in the target table. The target table must also exist before you execute the query.
Try SELECT INTO, this will generate the temp table FROM the results of the subselect.
Otherwise you will have to specify the columns like:
INSERT INTO #temp ( column1, column2 ) VALUES ( SELECT col1, col2 FROM othertable )