How to insert in order by - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

How to insert in order by

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2013-09-30 : 20:00:17

if (@Type=4)
begin
set @StakePerBet=@Stake/7
INSERT INTO tbl_BetSlipSystem(BetSlipDetailId,Stake,Win,SystemBet)
SELECT @BetSlipDetailId,@StakePerBet,0.00,Belt
FROM
(
SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@B)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@A)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@C)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@A,@B)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@B,@C)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@A,@C)
FOR XML PATH('')
),1,2,' ')

UNION

SELECT Belt=STUFF((
SELECT ', ' + CAST(ID AS VARCHAR)
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Number,ID
FROM inserted
) AS a
WHERE a.Number IN (@A,@B,@C)
FOR XML PATH('')
),1,2,' ')

) AS T
end;

This code incorrectly inserted data in order.

How to insert data in order?

In this moment my data is wrong inserted by order by?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-30 : 20:10:15

The order of the data in a table is meaningless. What matters is when you SELECT the data out of the table and add an ORDER BY to display the ordering you want.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

programer
Posting Yak Master

221 Posts

Posted - 2013-10-01 : 03:15:27

I have a exceptional case and this is very important for me.

quote:
Originally posted by tkizer

The order of the data in a table is meaningless. What matters is when you SELECT the data out of the table and add an ORDER BY to display the ordering you want.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-01 : 12:36:58

The answer is still the same. Add an ORDER BY to SELECT statements to order the data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-01 : 12:48:27

To go along with what Tara said, you can insert a column with a counter (maybe IDENTITY or ROW_NUMBER) and use that when selecting the data.

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-01 : 16:47:33

You can add an ORDER BY to the SELECT statements used to INSERT the data. For assigning IDENTITY values, SQL will honor the ORDER BY on the INSERT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources