Post reply

Complicated insert

  • January 4, 2022 at 3:35 pm

    Go to Answer

    #3969775

    I want to insert values from Table1 to Table2

    Conditions: when thier is a location with "xx", the row needs to be inserted twice

    the Empno in table2 is  a primary key with identity seed.

    Expected Output

     

  • January 4, 2022 at 3:40 pm

    #3969776

    Does table 2 contain any data currently?

    If you need to control the value of EmpNo in table2, you should set it to be an INT, but not an IDENTITY.

    Can you write a SELECT query which pulls the results from table 1 that you wish to INSERT?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 4, 2022 at 3:49 pm

    #3969779

    Yes, table2 already has data.

    Table2 EmpNo is a PK with INT datatype and also has identity seed.

    I want a insert into select Query(EmpName, Dept, Loc) where I can get all the rows, but the ones with location "xx", needs to be inserted twice.

  • January 4, 2022 at 4:00 pm

    Answer

    #3969780 

    SELECT ca1.*
    FROM dbo.Table1 t1
    CROSS APPLY (
        SELECT EmpNo, EmpName, DeptName, Location
        UNION ALL
        SELECT EmpNo, EmpName, DeptName, Location
        WHERE DeptName = 'xx'
    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • January 4, 2022 at 7:09 pm

    #3969868

    Thnk u, this resolved my issue

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply