Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Unanswered: inserting data from multiple Select statements

    Hi
    I am trying to load a table with a subset of information from another table. If i use the following syntax with one 'select statement' I do not have any problems:

    INSERT INTO tblTest (vGroup, iDataCnt )
    SELECT vGroup, count(*) FROM tblData1
    GROUP BY vGroup


    The problem arises when I want to pull data using multiple select statements. EX:

    INSERT INTO tblTest (vGroup, iDataCnt )
    SELECT vGroup FROM tblData1
    SELECT Count(*) from tblOther1


    I just cannot figure out the syntax. Does anyone know how to do this??
    (this is a snapshot of data, so a view will not work).

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you want to insert from more than one table use UNION

    INSERT myTbl (col1,col2)
    SELECT val1,val2
    FROM valTbl
    UNION
    SELECT arg1,arg2
    FROM argTbl

    The 2 SELECTs must have the same number of columns, if the don't then create a dummy column

    INSERT myTbl (col1,col2)
    SELECT val1,val2
    FROM valTbl
    UNION
    SELECT arg1,0
    FROM argTbl
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Posts
    3
    I need the two different selects to go into two different columns in the same row of data, so the Union solution will not work.

  4. #4
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    Multiple inserts

    Hi,

    I am making following assumption
    1) tblData1 table is a Reference and vGroup code is unique
    2) tblOther1 is a transaction table where vGroup can have multiple records

    So try this SQl statement

    INSERT INTO tblTest (vGroup, iDataCnt )
    SELECT vGroup, (select count(*) from tblOther1 a where a.vGroup = b.vGroup) FROM tblData1 b

    In case assumptions are not write please do let me know or reply to forum with details explaining your requirements

    best of luck
    Deepak Khattar

Posting Permissions

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