Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: insert data into 2 tables

    Hi All,

    In SQL Server, Can we load data into two table from a single table in a single shot?

    something like we do it as below:

    select * into <tname2> from <tname1>

    IS there any way to achieve this?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Do you mean:

    INSERT INTO table1
    SELECT * FROM table2

    ?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    Yes!

    is there any way to insert into 2 tables in the same way or using any other alternative method.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Do you mean inserting data in two different tables with only one single INSERT statement? I do not think it is possible.
    But why do you need something like this? If you need to have one single logical unit of work, just use transactions:
    BEGIN TRANS
    INSERT 1
    INSERT 2
    COMMIT
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    INSERT INTO dbo.t1
        OUTPUT inserted.*
            INTO dbo.t2
    SELECT 'this is the data inserted into t1 and t2'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I know I've seen that syntax before.....

    BUT

    M$ is such a hack
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Peso recently blogged about this: How to insert into two tables in one statement
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You mean exactly as per post #5
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2009
    Posts
    37
    use this
    INSERT INTO table1
    SELECT * FROM table2

Posting Permissions

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