INSERT (Level 2)
| SQL Server 6.x | SQL Server 2000 |
|---|---|
An INSERT x SELECT INTO Y statement ignored table Y and inserted the SELECT results into table X, as shown.
|
The INSERT...SELECT INTO syntax is retained only when the compatibility setting is equal to 60 or 65. If the compatibility setting is 70 or 80 and a similar query is executed, SQL Server returns a syntax error.
Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for queries using INSERT ...SELECT INTO syntax. Otherwise, use a compatibility setting of 80. |
| In an INSERT statement, a SELECT statement returning a scalar value was allowed in the VALUES clause. | The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.
Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for using a SELECT statement in the VALUES clause of an INSERT statement. Otherwise, use a compatibility setting of 80. |
| A ROLLBACK statement in a stored procedure referenced in an INSERT table EXEC procedure statement caused the INSERT to be rolled back, but the batch continued. | A ROLLBACK statement in the stored procedure referenced by an INSERT...EXEC statement causes the entire transaction to be rolled back and the batch stops executing. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.
Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for ROLLBACK statement behavior inside an INSERT...EXEC statement. Otherwise, use a compatibility setting of 80. |