how to add new row using existing row

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

General SQL Server Forums

New to SQL Server Programming

how to add new row using existing row

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-24 : 13:12:56

How I can add a new row to a table using existing row
for eg I have rec as below

1 C0001 aak.tif Y 5
6 C0006 aak.tif Y 1
7 C0007 aak.tif Y 2
9 C0009 aak.tif Y 1

For the above available table I want to add records as below,
which is the output I am looking for(I have 50K records).

1 C0001 aak.tif Y 5
2 C0002 aak.tif Y
3 C0003 aak.tif Y
4 C0004 aak.tif Y
5 C0005 aak.tif Y
6 C0006 aak.tif Y 1
7 C0007 aak.tif Y 2
8 C0008 aak.tif Y
9 C0009 aak.tif Y 1

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:17:55

[code]select t.firstfield + v.number,
t.secondfield,
t.thirdfield,
case when v.number = 0 then t.lastfield else null end
from yourtable t
cross join master..spt_values v
where v.type='p'
and v.number between 0 and t.lastfield -1
[/code]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-24 : 13:43:05

Thank you very much Visakh, I would like to make it generic. so that I can run it in oracle as well( ie with out using pt_values)

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:44:43

quote:
Originally posted by aakcse

Thank you very much Visakh, I would like to make it generic. so that I can run it in oracle as well( ie with out using pt_values

Thanks again.

then use a tally table instead

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-24 : 13:52:59

I am not aware of tally table (:

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:54:07

http://www.sqlservercentral.com/articles/T-SQL/62867/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-24 : 15:01:20

Thanks Visakh,

But the above query will is not working for second colum, need some more modification...

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:33:43

quote:
Originally posted by aakcse

Thanks Visakh,

But the above query will is not working for second colum, need some more modification...

Regards,
aak

why? whats the error?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources