September 7, 2010 at 5:15 pm
Using something like
CREATE TABLE tab1 (
ID INT Identity(1,1)
, Col1 int
)
INSERT INTO tab1
SELECT Col1 FROM tab2
Does it guaranteed me that new rows inserted in tab1 (from tab2) will be inserted in the same order as the "SELECT Col1 FROM tab2" return them?
Will I be able to do the following match after:
First row from "SELECT Col1 FROM tab2" = first new row inserted in tab1
Second row from "SELECT Col1 FROM tab2" = second new row inserted in tab1
Ex: tab 1 MAX ID is 4 (next Identity value is 5)
If I insert 4 rows in tab1,
Will tab1 rows 5 match "SELECT Col1 FROM tab2" row 1
and tab1 rows 6 match "SELECT Col1 FROM tab2" row 2
and tab1 rows 7 match "SELECT Col1 FROM tab2" row 3
and tab1 rows 8 match "SELECT Col1 FROM tab2" row 4 ?
I've read BOL and found nothing on that. I know about Identity gap when a rollback occurs but I'm unsure about that case if something can, while SQL is processing the mass insert, modify the ID sequence generated.
September 7, 2010 at 5:19 pm
I've never seen a situation where it didn't unless the clustered indexing was changed (since you don't have an order by) or there's something strange going on with the identity settings.
So, in theory, yes, you should be able to do:
select a1.id, b2.id
FROM tab1 AS a1
JOIN tab2 AS b2
ON a1.id - 4 = b2.id
This would be hell on your indexing but it should be consistent, unless someone reseeds when you're not looking... 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 7, 2010 at 5:33 pm
Megistal (9/7/2010)
Using something likeCREATE TABLE tab1 (
ID INT Identity(1,1)
, Col1 int
)
INSERT INTO tab1
SELECT Col1 FROM tab2
Does it guaranteed me that new rows inserted in tab1 (from tab2) will be inserted in the same order as the "SELECT Col1 FROM tab2" return them?
Will I be able to do the following match after:
First row from "SELECT Col1 FROM tab2" = first new row inserted in tab1
Second row from "SELECT Col1 FROM tab2" = second new row inserted in tab1
Ex: tab 1 MAX ID is 4 (next Identity value is 5)
If I insert 4 rows in tab1,
Will tab1 rows 5 match "SELECT Col1 FROM tab2" row 1
and tab1 rows 6 match "SELECT Col1 FROM tab2" row 2
and tab1 rows 7 match "SELECT Col1 FROM tab2" row 3
and tab1 rows 8 match "SELECT Col1 FROM tab2" row 4 ?
I've read BOL and found nothing on that. I know about Identity gap when a rollback occurs but I'm unsure about that case if something can, while SQL is processing the mass insert, modify the ID sequence generated.
The short answer to this is Yes.
The longer answer is that since your SELECT Col1 FROM tab2 doesn't have an order by clause, there is no guaranteed order - SQL will get your results in the most efficient way that it can.
If you want to guarantee that the records match on their ID column, then look into using SET IDENTITY_INSERT tab1 ON, and insert the values from tab2: INSERT INTO tab1 (ID, Col1)
SELECT ID, Col1 FROM tab2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 7, 2010 at 6:01 pm
Thanks to both of you! This will help me very much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply