从一个表存储过程来插入数据到另一个具有相同的列名(Stored Procedure to Inser

我有一个主表和一些较小的表。

• Master表有C1 | C2 | C3 | C4 | C5 | C1 | C2 | C3 | C4 | C5 |
• 小表有C1 | C2 | C3 | C1 | C2 | C3 |

还@C1 （即具有C1在值相匹配的值的变量Master表。

列名两个表匹配。 我想创建一个存储过程从插入值Master表（ C1 ， C2 ，和C3 ），以更小的表（ C1, C2, C3 ）。

我的努力：

Create proc Schema.Proc
(@C1 int)
AS
BEGIN
INSERT INTO SmallTable
(C1, C2, C3) --- Columns of smaller table
Values (SELECT C1, C2, C3 ---Columns of Master table
FROM MasterTable)
WHERE C1 = @C1 --- Where value of C1 of Master table matches the value of @C1
END

请帮忙

谢谢

Answer 1:

您需要使用INSERT INTO ... SELECT .....语法-没有VALUES关键字参与：

CREATE PROCEDURE Schema.Proc
   (@C1 int)
AS
BEGIN
    INSERT INTO SmallTable(C1, C2, C3) --- Columns of smaller table
        SELECT C1, C2, C3 ---Columns of Master table
        FROM MasterTable
        WHERE C1 = @C1 --- Where value of C1 of Master table matches the value of @C1
END

Answer 2:

你是接近！ 只要C1，C2和C3是相同的数据类型，这应该工作。

Create proc Schema.Proc
(@C1 int)
AS
BEGIN
INSERT INTO SmallTable
(C1, C2, C3) --- Columns of smaller table
SELECT C1, C2, C3 ---Columns of Master table
FROM MasterTable
WHERE C1 = @C1
END

Answer 3:

CREATE OR REPLACE PROCEDURE P_INSERT(U_ID NUMBER)
AS
BEGIN
INSERT INTO X(ID,NAME,SALARY)--X IS TABLE NAME 
SELECT ABC_ID,NAME,SALARY FROM ABC
WHERE ABC_ID=U_ID;--ABC_ID IS SAME VALUE OF U_ID
END;

文章来源: Stored Procedure to Insert Data from one table to another with same column names