1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Rep Power

    Question Insert w/Subselect format question

    I'm working on quite a problem here.

    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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    High above the mucky-muck (Columbus, OH)
    Rep Power
    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 )

IMN logo majestic logo threadwatch logo seochat tools logo