none
T-SQL: Using INSERT INTO or INSERT Statement with INSERT clause to populate Table Variable

    Question

  • I am inserting records returned by a Select statement into a table variable.  Can anyone tell me which of the two syntax options to use?

    Option A:

       Declare @AuditTable TABLE (ResponsibleDept nchar(10), ResponsiblePerson int)

       INSERT INTO @AuditTable(ResponsibleDept, ResponsiblePerson)

                SELECT OurFirm, ProjectMgr

                 FROM JCJMPM WHERE OurFirm IS NULL

    Option B:

       Declare @AuditTable TABLE (ResponsibleDept nchar(10), ResponsiblePerson int)

                SELECT OurFirm, ProjectMgr

                 FROM JCJMPM WHERE OurFirm IS NULL

                 INTO @AuditTable(ResponsibleDept, ResponsiblePerson)

    I'm new to table variables, just in case that isn't already obvious.

    Regards,

    ...bob sutor


    Bob Sutor


    • Edited by ConstPM Sunday, October 06, 2013 8:38 PM correction
    Sunday, October 06, 2013 8:35 PM

Answers

  • Hi,

    You can use Option #1 and that will create a new table but if you want to insert into an existing table use Option #2

    Good work , Please don't forget to mark as answered or helpful if the response helped you.

    • Marked as answer by ConstPM Monday, October 07, 2013 6:51 AM
    Monday, October 07, 2013 6:43 AM

All replies

  • Hi,

    Let's try this syntaxe your table result will be a temporary table not a variale, i think that the second syntaxe is not correct :

    SELECT *
    INTO #AuditTable
    FROM JCJMPM;

    Hope that can help;



    • Edited by KH MR Sunday, October 06, 2013 9:08 PM
    • Proposed as answer by KH MR Sunday, October 06, 2013 9:09 PM
    Sunday, October 06, 2013 9:00 PM
  • OK--A temporary table will not work.  I'll discard the idea of using a Table Variable.  I simply want to insert a result set returned by a Select statement into an existing table (udProcessAudit).  So would I use syntax below?

    Option #1:

     SELECT OurFirm, ProjectMgr

             INTO udProcessAudit(ResponsibleDept, ResponsiblePerson)           

             FROM JCJMPM WHERE OurFirm IS NULL

     


    Bob Sutor

    Monday, October 07, 2013 12:23 AM
  • To further clarify.  It appears the Option #1 syntax will create a new table, therefore, I assume I would use the following syntax:

    Option #2:

    INSERT INTO udProcessAudit (ResponsibleDept, ResponsiblePerson)

      SELECT OurFirm, ProjectMgr

      FROM JCJMPM WHERE OurFirm IS NULL


    Bob Sutor

    Monday, October 07, 2013 12:31 AM
  • Yes, this works.

    What actually you are trying?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 07, 2013 2:00 AM
  • Hi,

    You can use Option #1 and that will create a new table but if you want to insert into an existing table use Option #2

    Good work , Please don't forget to mark as answered or helpful if the response helped you.

    • Marked as answer by ConstPM Monday, October 07, 2013 6:51 AM
    Monday, October 07, 2013 6:43 AM