SQL Server创建5列数据表报错求助：语法错误排查及正确实现

Let's walk through the problems in your code and get it working properly:

1. The Immediate Syntax Error #

The "Incorrect syntax near object ':'" error comes directly from your time default value. In SQL Server, time literals need to be wrapped in single quotes—without them, the database misinterprets the colons as invalid syntax. You’ll need to change DEFAULT 08:00:00 to DEFAULT '08:00:00'.

2. Mismatched Column Type for d #

Your requirement specifies d should be an int with a default of NULL, but your code defines it as nvarchar(5). We’ll fix this type mismatch to align with your needs.

3. Proper IdP Configuration #

Since you need IdP to hold values from 1 to 450, it makes sense to mark it as non-null (and ideally a primary key) to enforce uniqueness. We’ll also add an automatic way to generate these 450 rows using a recursive CTE, so you don’t have to insert each row manually.

4. Avoiding Reserved Table Names #

While [Table] works with square brackets, it’s a reserved keyword in SQL Server. Using a more descriptive name (like ScheduleData) makes your code clearer and avoids future confusion.

Corrected Full SQL Code #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Create the table with corrected column definitions
CREATE TABLE [dbo].[ScheduleData](
    [IdP] [int] NOT NULL PRIMARY KEY, -- Enforce unique, non-null values
    [a] [time](0) DEFAULT '08:00:00', -- Time literal wrapped in quotes
    [b] [int] DEFAULT 1,
    [c] [int] DEFAULT 0,
    [d] [int] DEFAULT NULL -- Corrected to int type per your requirement
) ON [PRIMARY]
GO

-- Insert 450 rows with IdP from 1 to 450 automatically
WITH IdRange AS (
    SELECT 1 AS IdP
    UNION ALL
    SELECT IdP + 1 FROM IdRange WHERE IdP < 450
)
INSERT INTO [dbo].[ScheduleData] (IdP)
SELECT IdP FROM IdRange
OPTION (MAXRECURSION 450); -- Allow recursion to generate all 450 values
GO

What This Does: #

• The table now fully matches your column type and default value requirements.
• The recursive CTE generates the sequence of IdP values from 1 to 450 and inserts them. Since the other columns have defaults, we only need to specify IdP in the INSERT—SQL Server will fill in the rest automatically.
• Setting IdP as a primary key ensures you can’t have duplicate values, which aligns with needing exactly 1-450 unique entries.

内容的提问来源于stack exchange，提问作者ZombieDivision