T-SQL Query help

Transact-SQL
1

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

2

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

image
image925×294 5.43 KB

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

3

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

4

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.

5

Hi

Please Google search

You will find lots of articles

Screenshot_2019-12-26-10-47-38-27
Screenshot_2019-12-26-10-47-38-271080×2340 288 KB

6

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

image
image925×668 16.5 KB

7

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?

8

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.

9

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