Insert records into one table form other table

Question

Post reply

Insert records into one table form other table

komal145

SSCrazy Eights

Points: 9915
More actions
August 14, 2012 at 8:30 pm

#260758

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.

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

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

komal145 SSCrazy Eights Points: 9915 More actions · Answer 3

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.

rVadim Hall of Fame Points: 3969 More actions · Answer 4

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.