Insert with order by (keeping an order)

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

SQL Server Development (2000)

Insert with order by (keeping an order)

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2009-12-15 : 11:53:08

Hi all

I have two Temporay Tables #Table1 and #Table2
and one New Table Named MainTable
i Would Like to create the MainTable like this.

Get the first row in #table1, then Get All Rows in #table2
where Invoice1 = Invoice2

NOw i am using cursor, i trying to do this without cursor

See at the end of this email the desired result.
Tks
Carlos Lages

#Table1

Invoice1 ,Type , Desc1 (Varchar(255))
00123 C190 |121434|abcd|asgdag||465746A|ada0032|
00124 C190 |1455|ad|asgdag||465746A|ada067667|
00125 C190 |12343|bvbr|hjyjy7||445465746A|ada0032|000000000|
etc

#Table2

(key) Varchar(255)
Invoice2 Type Desc2
00123 C195 |C001|FRUTIS|123434|78987878787|12,00|
00123 C195 |X004|FISH|123434|78987878787|32,00|
00123 C195 |Y0034333|TIRE|123434|78987878787|1112,00|
00123 C195 |F01|FRUTIS|STEEL|78987878787|12,44|

00124 C195 |P001|XXXX|123434|78987878787|12,00|
00124 C195 |D004|YYY|123434|78987878787|32,00|
00124 C195 |YSS4333|DDDDD|123434|78987878787|1112,00|

00125 C195 |HHP0|XROPO|123434|78987878787|14,00|
00125 C195 |SEDR|YRLOL|123434|78987878787|34,00|
00125 C195 |YCV CV|HHYH|123434|78987878787|1512,00|

---------

The Main table

Invoice ,Type , Desc (Varchar(255))
00123 C190 |121434|abcd|asgdag||465746A|ada0032|
00123 C195 |C001|FRUTIS|123434|78987878787|12,00|
00123 C195 |X004|FISH|123434|78987878787|32,00|
00123 C195 |Y0034333|TIRE|123434|78987878787|1112,00|
00123 C195 |F01|FRUTIS|STEEL|78987878787|12,44|

00124 C190 |1455|ad|asgdag||465746A|ada067667|
00124 C195 |P001|XXXX|123434|78987878787|12,00|
00124 C195 |D004|YYY|123434|78987878787|32,00|
00124 C195 |YSS4333|DDDDD|123434|78987878787|1112,00|

00125 C190 |12343|bvbr|hjyjy7||445465746A|ada0032|000000000|
00125 C195 |HHP0|XROPO|123434|78987878787|14,00|
00125 C195 |SEDR|YRLOL|123434|78987878787|34,00|
00125 C195 |YCV CV|HHYH|123434|78987878787|1512,00|

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 11:58:49

[code]Insert into Maintable
(Invoice ,Type , Desc)
Select Invoice1 ,Type , Desc1 from #table1
UNION ALL
Select Invoice2 ,Type , Desc1 from #table2[/code]

EDIT: You need to worry about the ORDER only when you SELECT from the table.

Clages1
Yak Posting Veteran

69 Posts

Posted - 2009-12-16 : 05:41:54

Sorry,
but this solutions
will get all rows from #table1
union all all rows from #table2

this is not what i want.

i need

Merge the two tables

get first row from #Table1 Invoice 123
then get all rows in #table2 where invoice = 123

and so on

tks

Clages

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-16 : 05:45:21

quote:
Originally posted by Clages1

Sorry,
but this solutions
will get all rows from #table1
union all all rows from #table2

this is not what i want.

i need

Merge the two tables

get first row from #Table1 Invoice 123
then get all rows in #table2 where invoice = 123

and so on

tks

Clages

Then USe TOP operator

Insert into Maintable
(Invoice ,Type , Desc)
Select top 1 Invoice1 ,Type , Desc1 from #table1 where <condition>
UNION ALL
Select Invoice2 ,Type , Desc1 from #table2 where <condition>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 07:14:30

quote:
Originally posted by senthil_nagore

quote:
Originally posted by Clages1

Sorry,
but this solutions
will get all rows from #table1
union all all rows from #table2

this is not what i want.

i need

Merge the two tables

get first row from #Table1 Invoice 123
then get all rows in #table2 where invoice = 123

and so on

tks

Clages

Then USe TOP operator

Insert into Maintable
(Invoice ,Type , Desc)
Select top 1 Invoice1 ,Type , Desc1 from #table1 where <condition>
UNION ALL
Select Invoice2 ,Type , Desc1 from #table2 where <condition>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

That is not

Insert into Maintable
(Invoice ,Type , Desc)
Select Invoice1 ,Type , Desc1 from #table1
UNION ALL
Select Invoice2 ,Type , Desc1 from #table2
ORDER BY Invoice1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources