February 7, 2016 at 3:12 am
q1) where ever i need order by to assign unique and sequencial number i use following while insert , so if insert , inserts in any order that will not make any difference .
but with row_number i have used one more "select * from ( ......row_num) a ", is it required or only one select with row_number will do the task?
--sdate, brId is unique.
CREATE TABLE #Tmp1 (
Tmp1 INT NOT NULL,
sdate datetime null,
brId BIGINT NULL
)
INSERT INTO #Tmp1
(Tmp1,
brId,
sdate
)
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY sdate, brId) AS Tmp1,
brId
,sdate
FROM #sd
) a
yours sincerlye
<html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/08fe03c6-93d5-49a9-b4da-f78f9fe44a4e/insert-into-does-not-insert-in-order?forum=transactsql" id="link64_adl_tabid" style="display:none;">19</html:div>
32
February 7, 2016 at 3:58 am
I'm not sure I understand the question. Can you please post a full repro script?
(I.e., CREATE TABLE statements for all tables involved, INSERT statements with just enough rows to show the problem, the statement you executed, the results you got, and the results you wanted to get).
An explanation of what you are trying to achieve and why you are doing this can also help.
February 7, 2016 at 7:01 am
rajemessage 14195 (2/7/2016)
q1) where ever i need order by to assign unique and sequencial number i use following while insert , so if insert , inserts in any order that will not make any difference .but with row_number i have used one more "select * from ( ......row_num) a ", is it required or only one select with row_number will do the task?
--sdate, brId is unique.
CREATE TABLE #Tmp1 (
Tmp1 INT NOT NULL,
sdate datetime null,
brId BIGINT NULL
)
INSERT INTO #Tmp1
(Tmp1,
brId,
sdate
)
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY sdate, brId) AS Tmp1,
brId
,sdate
FROM #sd
) a
yours sincerlye
<html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/08fe03c6-93d5-49a9-b4da-f78f9fe44a4e/insert-into-does-not-insert-in-order?forum=transactsql" id="link64_adl_tabid" style="display:none;">19</html:div>
32
Using this and your similar post at http://www.sqlservercentral.com/Forums/Topic1759108-3077-1.aspx, I think I understand what you're trying to do. If I'm reading post posts correctly, you want to insert rows into a temp table in a particular order and you wan them to have sequential ID numbers.
INSERT INTO #Tmp1(Tmp1, brId, sdate)
SELECT ROW_NUMBER() OVER(ORDER BY sdate, brId), brId, sdate
FROM #sd;
Please understand that the physical order of the table doesn't matter. You can process rows in whatever order you want. However, a set-based approach will out-perform a RBAR approach and also scales better. If the reason you want the Tmp1 values sequential is to loop through them, at least consider set-based alternatives.
February 7, 2016 at 7:46 am
Thank u,
in 99 percent cases, i use set based approach , this was quite old situation , so i was forced to use loop.
but my question was to have that extra select or not. the script u have posted is
INSERT INTO #Tmp1(Tmp1, brId, sdate)
SELECT ROW_NUMBER() OVER(ORDER BY sdate, brId), brId, sdate
FROM #sd;
so i am taking it in this way , that my rows will have unique sequqntial(with out gap) numbers ( order by sdate and brid ) so that i can use Tmp1
to loop in sequential manner
or to show reports from #tmp1 using order by tmp1 rather than (sdate and brid)
your sincerely
February 7, 2016 at 8:29 am
Again, if you really need to loop you can do so without temp table.
DECLARE MyCurs CURSOR LOCAL FAST_FORWARD
FOR SELECT brId, sdate
FROM Somewhere
ORDER BY sdate, brId;
February 7, 2016 at 1:11 pm
Glad it worked for you. Thanks for the feedback.
February 9, 2016 at 12:07 am
can i save tempdb space by using cursor or it will also make worktable in tempdb?
yours sincerely
February 9, 2016 at 4:14 am
All static and fast_forward cursors use tempdb to store the results of the query. Very similar to what you are now doing with an explicit temporary table, just more efficient.
Keyset and dynamic cursors use less or no tempdb at all, at the expense of a huge performance hit and some weird and wonderful effects when other connections do concurrent updates. You do not want or need any of those for your current problem.
Frankly, if your result set is big enough that you need to worry about the tempdb space taken, then you really must redesign your solution to not use a loop. Any code that loops over such a huge collection of rows will always perform poorly.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply