order is change at the time of insert

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)

order is change at the time of insert

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2007-03-16 : 04:05:43

I am facing some problem related to sequencing

I have original table and one temp table

original table is as follow

Key Name value

63871 Reimburse Amt
63871 Flat Rate 11
63871 Process Rule
63872 ASC DRG Code
63872 MDC Code
63872 DRG Reim Amt
63872 Reim Base Type
63872 Reim Base Value
63872 DRG Outlier Amt
63872 HIS Reimburse Pct 11

when I am trying to insert the above records in temp table
then it is entering the Name in Ascending order.
like as follow

tempkey tempname tempvalue
63871 Flat Rate 11
63871 Process Rule
63871 Reimburse Amt
63872 ASC DRG Code
63872 DRG Outlier Amt
63872 DRG Reim Amt
63872 HIS Reimburse Pct 11
63872 MDC Code
63872 Reim Base Type
63872 Reim Base Value

I don't want ascending order.I want same data like Original table in temp table.
I am useing following query

insert into temptable
select * from originaltable

Please tell me how I can maintain the sequence at the time of inserting the data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:36:13

Why are you concerned about the order in the tables?
It has absolutely no matter for you.

Peter Larsson
Helsingborg, Sweden

shubhada
Posting Yak Master

117 Posts

Posted - 2007-03-16 : 04:41:54

the order is as per the Business requirment.
if order is changed then business logic will change.
I must have to maintain it....

SQLTeam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:50:16

Not in the TABLES!

When you SELECT the data, you can to an ORDER BY.
Or, create a VIEW based in the table with an ORDER BY.

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-17 : 21:39:05

quote:
Originally posted by Peso

Or, create a VIEW based in the table with an ORDER BY.

Controversial statement there Peso.....
Does this always work? I recon it doesn't (I'd need time and inclination to prove it though). I seem to remember a while ago someone was tripped up over this. Seems like TOP 100 PERCENT is optimised out in SQL Server 2005 which means that an ORDER BY in an inline view is also removed (because as you well know, there is no order of a table or inline table/view).

Safest to ORDER BY on your query for sure, especially when the requirement changes and you end up joining in more tables anyway.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 05:02:02

This is a SQL 2000 forum.

If you really are concerned about the order in the TEMP table,

INSERT #Temp
SELECT Col1, Col2, ...
FROM SourceTable
ORDER BY SomeCol

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-18 : 20:17:32

Aw - come on. On the count of 3 - the only way to 100% determine the output order is to use ORDER BY on the select. We all know that.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources