Insert records into one table form other table

  • Hi friends,

    I have a situation where i need tocreate table and insert records into that table from other table.

    New table :

    Create table A

    (

    AId int,

    ACode varchar(30),

    ATimeStamp datetime,

    )

    Step 1: Need to load all records all the Acode with 'Runquote'

    Insert into tableA

    SELECT Aid

    , ACode

    ,MIN(ATimestamp) ATimestamp

    FROM dbo.ALog al

    WHERE AId > 0 AND ACode='RunQuote'

    GROUP BY

    Aid, ACode

    Step 2: Need to load all AId's that are not "runquote" but with acode 'Generatedocument'

    Insert into TableA

    SELECT Aid

    , ACode

    ,MIN(ATimestamp) ATimestamp

    FROM dbo.ALog al

    WHERE AId > 0 AND ACode='Generatedocument'

    and AID not in (select Aid from tableA)

    GROUP BY

    Aid, ACode

    This is what I am doing right now.Is there any other way that I can do this???? In stored procedure??? In a single load using TSQL??

    Thanks for Help.

  • To do it in a single load, you can use a query like

    ;WITH OrderedALog AS

    (

    SELECT

    Aid, ACode, ATimestamp,

    ROW_NUMBER() OVER (PARTITION BY Aid ORDER BY ACode DESC, ATimestamp) RowNum

    FROM

    dbo.ALog al

    WHERE

    AId > 0 AND ACode IN ('Generatedocument', 'RunQuote')

    )

    INSERT INTO

    dbo.A

    SELECT

    Aid, ACode, ATimestamp

    FROM

    OrderedALog

    WHERE

    RowNum = 1

  • This should replace both original queries:

    INSERT INTO tableA (Aid, ACode, ATimestamp)

    SELECT

    Aid

    ,ACode

    ,MIN(ATimestamp) ATimestamp

    FROM dbo.ALog al

    WHERE AId > 0

    AND ACode IN ('RunQuote', 'Generatedocument')

    GROUP BY ACode, Aid

    ORDER BY ACode, Aid

    - always TEST.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Issue is there is Aid with both "runquote" and "generatedocument" , need to load Aid with "Runquote",

    and then load the Aid's which are not "runquote" but with "Generatedocumnet"

    example: AID : 12345 has Acode as "runquote" and "Generatedocument" but need to have only one Aid:12345 with "runquote" in my new tableA.

  • komal145 (8/15/2012)


    Issue is there is Aid with both "runquote" and "generatedocument" , need to load Aid with "Runquote",

    and then load the Aid's which are not "runquote" but with "Generatedocumnet"

    example: AID : 12345 has Acode as "runquote" and "Generatedocument" but need to have only one Aid:12345 with "runquote" in my new tableA.

    Peter's code should do this. It will mark rows with ROW_NUMBER function and then load only those where RowNum = 1:

    12345 'runquote ' 1

    12345 'Generatedocument' 2

    23456 'runquote ' 1

    23456 'Generatedocument' 2

    34567 'Generatedocument' 1

    --Vadim R.

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

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