Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Unanswered: Storing result of query into a SQL table

    I have two tables. The first table (Table1) contains the original data. The second table (Table2) contains original data + additional data.

    I write a query to extract the additional data being added to the 2nd table. The query looks like this:

    SELECT *
    FROM Table2 LEFT OUTER JOIN Table1
    ON Table2.CUSTNO = Table1.CUSTNO
    WHERE Table2.CUSTNO
    NOT IN (SELECT Table1.CUSTNO
    FROM Table1 INNER JOIN Table2
    ON Table2.CUSTNO = Table1.CUSTNO)

    I am trying to store the result of the query to a new table (that does not exist), by using the:

    SELECT *
    INTO NewTable
    ON Table2.CUSTNO = Table1.CUSTNO
    WHERE Table2.CUSTNO
    NOT IN (SELECT Table1.CUSTNO
    FROM Table1 INNER JOIN Table2
    ON Table2.CUSTNO = Table1.CUSTNO)

    The server returns this error message: Server: Msg 2705, Level 16, State 3, Line 1
    Column names in each table must be unique. Column name 'CUSTNO' in table 'NewTable' is specified more than once.

    Anyone has any idea on how to store the result of the query to a new table? Also any better ideas on writing the query to extract additional data from Table 2?

    --Thanks

  2. #2
    Join Date
    Apr 2003
    Posts
    12
    Just as the error messages says : custno is mentioned 2 times in the result and column names must be unique in a table.

    Drop the * and reference the columns by name leaving and mention custno only once. Will work fine

  3. #3
    Join Date
    Mar 2003
    Posts
    6
    Thanks, Buckley!

    I actually tried referencing the table name in the Select statements and it works just fine:

    Select Table2.*
    INTO NewTable
    ON Table2.CUSTNO = Table1.CUSTNO
    WHERE Table2.CUSTNO
    NOT IN (SELECT Table1.CUSTNO
    FROM Table1 INNER JOIN Table2
    ON Table2.CUSTNO = Table1.CUSTNO)

    But I realize that referencing the colum names would work fine too!

    Thanks,

    Speedster

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •