Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert records into one table form other table Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 8:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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.

Post #1345108
Posted Wednesday, August 15, 2012 3:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:09 PM
Points: 1,598, Visits: 6,659
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

Post #1345183
Posted Wednesday, August 15, 2012 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1345189
Posted Wednesday, August 15, 2012 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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.
Post #1345320
Posted Wednesday, August 15, 2012 9:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 994, Visits: 2,227
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

Post #1345366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse