Need to insert an identity field in a temporary ta

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need to insert an identity field in a temporary ta

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-15 : 12:50:43

Hi,
I have the sql as shown below. Here I am inserting the result of the sql to
a new temporary table #temp3c. However the new temporary table created needs an
identity field in addition to the fields that are coming from the query. How
does on do this. I need this ID in order to eliminate duplicate rows (both
finishedpartno and projectno as duplicate).

SQL:
SELECT #temp3b.FinishedPartNo, #temp3b.ProjectNo, #temp3b.EstimateNo,
#temp3b.estimatedesc,
#temp3b.EstimateTime, #temp3b.customername, #temp3b.engineerName,
#temp3b.plantname ,#temp3b.FamilyCode, #temp3b.QtyPer, #temp3b.QuoteUnitCost,
#temp3b.FGCost, #temp3b.YearlyQty, #temp3b.aqty, #temp3b.annVol1,
#temp3b.salesperson,
#temp3b.FirstReleaseQty, #temp3b.FirstMaterialUnitCost,
#temp3b.FirstTotalLaborCost, #temp3b.FirstTotalUnitCost,
#temp3b.LastReleaseQty, #temp3b.LastMaterialUnitCost,
#temp3b.LastTotalLaborCost, #temp3b.LastTotalUnitCost
into #temp3c
FROM
#temp3b INNER JOIN #temp3a ON
#temp3b.FinishedPartNo = #temp3a.FinishedPartNo AND
#temp3b.ProjectNo = #temp3a.ProjectNo

select * from #temp3c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 12:53:59

[code]SELECT IDENTITY(int,1,1) AS IDCol,
#temp3b.FinishedPartNo, #temp3b.ProjectNo, #temp3b.EstimateNo,
#temp3b.estimatedesc,
#temp3b.EstimateTime, #temp3b.customername, #temp3b.engineerName,
#temp3b.plantname ,#temp3b.FamilyCode, #temp3b.QtyPer, #temp3b.QuoteUnitCost,
#temp3b.FGCost, #temp3b.YearlyQty, #temp3b.aqty, #temp3b.annVol1,
#temp3b.salesperson,
#temp3b.FirstReleaseQty, #temp3b.FirstMaterialUnitCost,
#temp3b.FirstTotalLaborCost, #temp3b.FirstTotalUnitCost,
#temp3b.LastReleaseQty, #temp3b.LastMaterialUnitCost,
#temp3b.LastTotalLaborCost, #temp3b.LastTotalUnitCost
into #temp3c
FROM
#temp3b INNER JOIN #temp3a ON
#temp3b.FinishedPartNo = #temp3a.FinishedPartNo AND
#temp3b.ProjectNo = #temp3a.ProjectNo[/code]

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-15 : 14:15:20

Thanks visakh16 for your help. I appreciate it. Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources