SQLTeam.com | Weblogs | Forums

T-SQL Query help

Hi Guys,
Here is my sample table and data.

Create table #TempInvoice
(
id int identity (1,1),
Tkey int,
Status varchar(25),
Amount float
)

Insert into #TempInvoice (Tkey,Status,Amount)
VALUES (2331,'SecondTry',23.00),
(2331,'ThirdTry',21.00),
(2331,'Transfer',18.00)

Select * from #TempInvoice

Drop table #TempInvoice

However, Here what I want in my select statement.

ID,Tkey,Status,Amount
1,2331,SecondTry,23
2,2332,ThirdTry,21
3,2331,Transfer,18
4,2331,First,23

I want to just add the last row with same information just changing the status to "First" any advice? The amount could be coming from Top 1

hi

i tried to do this ... hope this helps :slight_smile:

The ID is coming as 1 instead of 4 is that ok ???? or you need 4 only !!!

please click arrow to the left for DROP Create Data
Drop table #TempInvoice

Create table #TempInvoice
(
id int identity (1,1),
Tkey int,
Status varchar(25),
Amount float
)

Insert into #TempInvoice (Tkey,Status,Amount)
VALUES (2331,'SecondTry',23.00),
(2331,'ThirdTry',21.00),
(2331,'Transfer',18.00)

select 'data',* from #TempInvoice
go

please click arrow to the left for SQL
Select       id,Tkey,Status ,Amount     from #TempInvoice
union all 
select top 1 id,Tkey,'First',Amount     from #TempInvoice
go

image

hi

if you want 4 at the end for ID then New SQL

please click arrow to the left for New SQL
Select         id      ,Tkey ,Status ,Amount   from #TempInvoice
union all 
select top 1   maxid+1 ,Tkey ,'First',Amount   from #TempInvoice ,(select MAX(id) as maxid from #TempInvoice ) a 
go

image

Hi harishgg1,

Thank you for your reply. I know I can use "Union All" to accomplish what I want. Is there any other way I can use to accomplish this ?

Once again thank you for your reply.

Hi

Please Google search

You will find lots of articles

hi

Another way is to

Insert into table ... the TOP 1 row

please click arrow to the left for DROP CREATE data
Drop table #TempInvoice

Create table #TempInvoice
(
id int identity (1,1),
Tkey int,
Status varchar(25),
Amount float
)

Insert into #TempInvoice (Tkey,Status,Amount)
VALUES (2331,'SecondTry',23.00),
(2331,'ThirdTry',21.00),
(2331,'Transfer',18.00)

select 'data',* from #TempInvoice
go
please click arrow to the left for INSERT
insert into 
   #TempInvoice
select 
   top 1   Tkey ,'First',Amount   
from 
   #TempInvoice

Hi Sonus,

Sonu619

9h

Hi harishgg1,

Thank you for your reply. I know I can use "Union All" to accomplish what I want. Is there any other way I can use to accomplish this ?

What's wrong with a UNION ALL? Why do you want an alternative?

Hi Wim,
I am sure you know the above data was a sample data example. In a real scenario I have to Loop through the 1.2 Mi rows to populate a source report. Any way, thank you.

Hi Sonu619

If you have to loop through
1.2 million records

One idea is to break it up into
Small bits ... Amount of rows
And build a separate report table output
from that. .

https://stackoverflow.com › questions
SQL Loop through 8 million record and update them - Stack Overflow