Post reply

Query help for inserting data

  • July 9, 2018 at 12:48 am

    #365922

    Hi

    I am writing a procedure to move data from one table to another along with some additional fields. here I have to get one additional field based on one field in source.

    Source   Destination
    C1  C2   C1 C2 C3

    TableX
    C2 C3

    I want to move data from Source to destination and while inserting to Destination I have to get new column C3 value from TableX based on C2 value.

    Please help.

    Thanks.

  • July 9, 2018 at 1:48 am

    #1996726

    KGNH - Monday, July 9, 2018 12:48 AM

    Hi

    I am writing a procedure to move data from one table to another along with some additional fields. here I have to get one additional field based on one field in source.

    Source   Destination
    C1  C2   C1 C2 C3

    TableX
    C2 C3

    I want to move data from Source to destination and while inserting to Destination I have to get new column C3 value from TableX based on C2 value.

    Please help.

    Thanks.

    Is it direct pull/mapping of C3 value based on C2 value or is there any transformation logic  involved. I mean is it derived column ?

    Saravanan

  • July 9, 2018 at 3:53 am

    #1996741

    saravanatn - Monday, July 9, 2018 1:48 AM

    KGNH - Monday, July 9, 2018 12:48 AM

    Hi

    I am writing a procedure to move data from one table to another along with some additional fields. here I have to get one additional field based on one field in source.

    Source   Destination
    C1  C2   C1 C2 C3

    TableX
    C2 C3

    I want to move data from Source to destination and while inserting to Destination I have to get new column C3 value from TableX based on C2 value.

    Please help.

    Thanks.

    Is it direct pull/mapping of C3 value based on C2 value or is there any transformation logic  involved. I mean is it derived column ?

    Hi Saravanan.

    Based on C2 value in Source we should get C3 value from table 3 and insert into destination. Here just we have to fetch C3 from a third table and insert into destination.

    Thanks.

  • July 9, 2018 at 10:46 am

    #1996824

    KGNH - Monday, July 9, 2018 12:48 AM

    Hi

    I am writing a procedure to move data from one table to another along with some additional fields. here I have to get one additional field based on one field in source.

    Source   Destination
    C1  C2   C1 C2 C3

    TableX
    C2 C3

    I want to move data from Source to destination and while inserting to Destination I have to get new column C3 value from TableX based on C2 value.

    Please help.

    Thanks.

    Have you ever worked with the OUTPUT clause of a SQL command?  You could possibly do a DELETE and store the deleted records into a temp table using the OUTPUT clause, then join the temp table to the TableX to get C3 and do your INSERT into DESTINATION table
    https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2012

  • July 9, 2018 at 2:29 pm

    #1996866

    so if i read you right then something like this would work i think assuming c2 in source would equal c2 in tablex

    INSERT INTO destination(c1,c2,c3)
    SELECT a.c1, a.c2, b.c3
    FROM source a
    LEFT JOIN tablex b ON a.c2 = b.c2

    you could inner join it as well but if there is no match you would be removing a row from the insertion., thats why i chose a left join on it.

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

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