When inserting multiple rows in a table, is there any guarantee that they go in in the order I specify? For instance, take the following:
DECLARE @blah TABLE
(
ID INT IDENTITY(1, 1),
Name VARCHAR(100) NOT NULL
);
INSERT INTO @blah (Name)
VALUES('Timmy'),
('Jonny'),
('Sally');
SELECT * FROM @blah
Is there any guarantee that Sally
will have a higher primary key than Timmy
?
The answer for this simple case is: "Yes". Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order.
The system generates an IDENTITY column value when the keyword DEFAULT is used as the insert_clause for the IDENTITY column.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement. To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.
The very similar question was asked before.
You can specify an ORDER BY
in the INSERT
.
If you do that, the order in which the IDENTITY
values are generated is guaranteed to match the specified ORDER BY
in the INSERT
.
Using your example:
DECLARE @blah TABLE
(
ID INT IDENTITY(1, 1) NOT NULL,
Name VARCHAR(100) NOT NULL
);
INSERT INTO @blah (Name)
SELECT T.Name
FROM
(
VALUES
('Timmy'),
('Jonny'),
('Sally')
) AS T(Name)
ORDER BY T.Name;
SELECT
T.ID
,T.Name
FROM @blah AS T
ORDER BY T.ID;
The result is:
+----+-------+
| ID | Name |
+----+-------+
| 1 | Jonny |
| 2 | Sally |
| 3 | Timmy |
+----+-------+
That is, Name
have been sorted and IDs have been generated according to this order. It is guaranteed that Jonny will have the lowest ID, Timmy will have the highest ID, Sally will have ID between them. There may be gaps between the generated ID values, but their relative order is guaranteed.
If you don't specify ORDER BY
in INSERT
, then resulting IDENTITY
IDs can be generated in a different order.
Mind you, there is no guarantee for the actual physical order of rows in the table even with ORDER BY
in INSERT
, the only guarantee is the generated IDs.
In a question INSERT INTO as SELECT with ORDER BY Umachandar Jayachandran from MS said:
The only guarantee is that the identity values will be generated based on the ORDER BY clause. But there is no guarantee for the order of insertion of the rows into the table.
And he gave a link to Ordering guarantees in SQL Server, where Conor Cunningham from SQL Server Engine Team says:
- INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
There is a link to MS knowledge base article in the comments in that post: The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause, which explains it in more details. It says:
If you want the
IDENTITY
values to be assigned in a sequential fashion that follows the ordering in theORDER BY
clause, create a table that contains a column with theIDENTITY
property and then run anINSERT ... SELECT ... ORDER BY
query to populate this table.
I would consider this KB article as an official documentation and consider this behaviour guaranteed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With