SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to insert in order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 09/30/2013 :  20:00:17  Show Profile  Reply with Quote
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

USA
35954 Posts

Posted - 09/30/2013 :  20:10:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 10/01/2013 :  03:15:27  Show Profile  Reply with Quote
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/

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 10/01/2013 :  12:36:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 10/01/2013 12:37:44
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 10/01/2013 :  12:48:27  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 10/01/2013 :  16:47:33  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000