T-SQL技术需求：将Table_2列名插入Table_1首行并生成Seq行号

Alright, let's solve this problem. You need to insert the column names of Table_2 as a single row into Table_1, with Seq set to 1. Below are solutions for different common databases, plus a simpler hardcoded option if your column names are fixed.

Hardcoded Solution (If Column Names Never Change) #

If you're certain Table_2's column names will always be Name, Column1Table2, Column2Table2, you can skip querying system tables and just insert the values directly:

INSERT INTO Table_1 (Seq, Name, Column1, Column2)
VALUES (1, 'Name', 'Column1Table2', 'Column2Table2');

Dynamic Solution (For When Column Names Might Change) #

If you want the query to automatically pull the current column names from Table_2 (in case they get renamed), use these database-specific queries:

MySQL/MariaDB #

We'll use the information_schema.COLUMNS table to fetch column names by their position:

INSERT INTO Table_1 (Seq, Name, Column1, Column2)
SELECT 
    1 AS Seq,
    (SELECT COLUMN_NAME FROM information_schema.COLUMNS 
     WHERE TABLE_NAME='Table_2' AND ORDINAL_POSITION=1) AS Name,
    (SELECT COLUMN_NAME FROM information_schema.COLUMNS 
     WHERE TABLE_NAME='Table_2' AND ORDINAL_POSITION=2) AS Column1,
    (SELECT COLUMN_NAME FROM information_schema.COLUMNS 
     WHERE TABLE_NAME='Table_2' AND ORDINAL_POSITION=3) AS Column2;

SQL Server #

Use the sys.columns catalog view to get column details:

INSERT INTO Table_1 (Seq, Name, Column1, Column2)
SELECT 
    1 AS Seq,
    (SELECT name FROM sys.columns 
     WHERE object_id = OBJECT_ID('Table_2') AND column_id=1) AS Name,
    (SELECT name FROM sys.columns 
     WHERE object_id = OBJECT_ID('Table_2') AND column_id=2) AS Column1,
    (SELECT name FROM sys.columns 
     WHERE object_id = OBJECT_ID('Table_2') AND column_id=3) AS Column2;

PostgreSQL #

Query information_schema.columns (note: table names are case-sensitive if created with quotes; adjust table_name accordingly):

INSERT INTO Table_1 (Seq, Name, Column1, Column2)
SELECT 
    1 AS Seq,
    (SELECT column_name FROM information_schema.columns 
     WHERE table_name='table_2' AND ordinal_position=1) AS Name,
    (SELECT column_name FROM information_schema.columns 
     WHERE table_name='table_2' AND ordinal_position=2) AS Column1,
    (SELECT column_name FROM information_schema.columns 
     WHERE table_name='table_2' AND ordinal_position=3) AS Column2;

Oracle #

Use the user_tab_columns view (table names are typically uppercase by default):

INSERT INTO Table_1 (Seq, Name, Column1, Column2)
SELECT 
    1 AS Seq,
    (SELECT column_name FROM user_tab_columns 
     WHERE table_name='TABLE_2' AND column_id=1) AS Name,
    (SELECT column_name FROM user_tab_columns 
     WHERE table_name='TABLE_2' AND column_id=2) AS Column1,
    (SELECT column_name FROM user_tab_columns 
     WHERE table_name='TABLE_2' AND column_id=3) AS Column2;

Each of these dynamic queries pulls the column names in their defined order from the database's system catalog, so it'll adapt if you rename Table_2's columns later.

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