Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    1

    Unanswered: Inserting more than 1 value

    I have the following code for SQL2000:
    It only works if the sub-query returns only 1 values.... anyone knows how I can solve this?
    thx a lot!!!

    INSERT INTO Table1(SourceName, CreatedBy, CreateDate, IsSuccess, datadate)
    SELECT 'Data_01', user, GetDate(), 1,
    (
    SELECT DISTINCT T1_Dummy.[TRANSACTION DATE]
    FROM T1
    WHERE T1.[TRANSACTION ID] NOT IN
    (
    SELECT T.TRANSACTION_ID
    FROM T
    )
    )
    FROM T1
    GROUP BY [Transaction Date]

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you solve it by making sure the subquery returns only one value

    note you have not defined the T1_DUMMY table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    161
    Is this what you want? It may not give you the results you desire.

    Code:
    INSERT INTO Table1(SourceName, CreatedBy, CreateDate, IsSuccess, datadate)
    SELECT 'Data_01', user, GetDate(), 1, T2.*
    FROM 
    (
    SELECT DISTINCT T1_Dummy.[TRANSACTION DATE]
    FROM T1
    WHERE T1.[TRANSACTION ID] NOT IN
    (
    SELECT T.TRANSACTION_ID
    FROM T
    )
    ) T2
    GROUP BY [Transaction Date]

    Bill

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the GROUP BY must contain all the non-aggregate column expressions in the SELECT list

    this means user plus all the columns in T2 (which is just one column, but anyway...)

    note that T1_DUMMY is still not defined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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