SQL Server复制表数据至新表及LIKE关键字语法错误求助

Hey there! Let's get your table structure copy sorted out, and fix that annoying syntax error along the way.

First, Why You're Getting That Error #

That Msg 156 error is telling you exactly what's wrong: you're using the LIKE keyword in a way SQL Server doesn't support. Chances are you tried something like this (a syntax that works in MySQL, but not SQL Server):

CREATE TABLE new_student_table LIKE student_constraint;

SQL Server has no clue how to interpret LIKE in the CREATE TABLE statement, hence the syntax error. Let's move on to the correct ways to copy your student_constraint table structure.

Method 1: Quick & Simple (Basic Structure Only) #

If you just need the column definitions (data types, nullability) and don't care about copying constraints (primary keys, foreign keys, indexes) right away, use SELECT INTO. This method also lets you optionally copy data if you remove the filter.

-- Creates a new empty table with the same column structure as student_constraint
SELECT *
INTO new_student_table
FROM student_constraint
WHERE 1 = 0; -- The 1=0 ensures no rows are copied, only the structure

Note: This won't copy primary keys, foreign keys, indexes, or default constraints—just the base column setup.

Method 2: Full Structure Copy (Including All Constraints) #

If you need every detail of the original table (constraints, indexes, triggers, etc.), the easiest way is to use SQL Server Management Studio (SSMS):

• Right-click your student_constraint table in Object Explorer
• Hover over Script Table as → CREATE To → New Query Editor Window
• In the generated script, replace the original table name with your new table name
• Execute the script

If you prefer doing it via T-SQL (for automation), you can query system views to build a dynamic creation script. Here's a simplified example that copies columns and primary keys:

DECLARE @OriginalTable NVARCHAR(128) = 'student_constraint';
DECLARE @NewTable NVARCHAR(128) = 'new_student_table';
DECLARE @CreateSQL NVARCHAR(MAX);

-- Build the CREATE TABLE statement with columns
SELECT @CreateSQL = 'CREATE TABLE ' + QUOTENAME(@NewTable) + ' (' + STUFF((
    SELECT ', ' + QUOTENAME(c.COLUMN_NAME) + ' ' +
           c.DATA_TYPE +
           CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE '' END +
           CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @OriginalTable
    ORDER BY c.ORDINAL_POSITION
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')';

EXEC sp_executesql @CreateSQL;

-- Add the primary key constraint
SELECT @CreateSQL = 'ALTER TABLE ' + QUOTENAME(@NewTable) + ' ADD CONSTRAINT PK_' + @NewTable + ' PRIMARY KEY (' + STUFF((
    SELECT ', ' + QUOTENAME(kc.COLUMN_NAME)
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = @OriginalTable AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ORDER BY kc.ORDINAL_POSITION
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')';

EXEC sp_executesql @CreateSQL;

You can extend this script to add foreign keys, indexes, or default constraints by querying additional system views like INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS or sys.indexes.

Recap #

• Ditch the LIKE syntax—it's not for SQL Server table creation
• Use SELECT INTO for a fast, basic structure copy
• Use SSMS-generated scripts or dynamic T-SQL for a full, exact copy of the table including all constraints

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