Tips | syntax in SQL Server online book (ssbol), locate sp_dbcmptlevel, and then find a datasheet that describes version compatibility between 6.0, 6.5, and 7.0
。 You can note that the INSERT SQL statement has the syntax shown below.
INSERT X
SELECT select_list into Y
Earlier SQL Server databases (version 6.0 or 6.5) can parse such statements correctly, but new SQL Server databases (7.0 or 8.0) cannot be parsed correctly. Although y in syntax does not have to be the recipient of the insert operation under any compatibility level setting, the old database allows such syntax to exist. This syntax is not enforceable in a new database because of its stricter syntax checking, yet this "strict requirements" makes the code more robust for users to write.
Here is a simple script that can run on SQL Server 7.0 or 2000, which shows how the syntax of the previous database passes the test of the old one but does not pass the new database check.
SET NOCOUNT off
Go
Use pubs
Go
IF EXISTS (SELECT * from sysobjects WHERE type =
' U ' and name = ' Test '
BEGIN
DROP TABLE Test
End
Go
CREATE TABLE Test (col1 int NULL, col2 int null)
Go
EXEC sp_dbcmptlevel Pubs, 65
Go
INSERT Test
SELECT 1, 1 into Y
Go
EXEC sp_dbcmptlevel Pubs, 70
Go
INSERT Test
SELECT 2, 1 into Y
Go
