SQL 将数据从一个表插入到另一个具有相同列名的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14204834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure to Insert Data from one table to another with same column names
提问by 007
I have one master table and few smaller table.
我有一张主桌和几张小桌。
Mastertable hasC1 | C2 | C3 | C4 | C5 |- A small table has
C1 | C2 | C3 |
Master表有C1 | C2 | C3 | C4 | C5 |- 一张小桌子有
C1 | C2 | C3 |
Also @C1(a variable that has a value which matches the value of C1 in Mastertable.
此外@C1（具有与Master表中 C1 值匹配的值的变量。
The column names matches for both table. I want to create a stored procedure which inserts values from Mastertable (C1, C2, and C3) to smaller table (C1, C2, C3).
两个表的列名都匹配。我想创建一个存储过程从插入值Master表（C1，C2，和C3更小的表）（ C1, C2, C3）。
My effort:
我的努力：
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
Please help
请帮忙
Thank you
谢谢
回答by marc_s
You need to use the INSERT INTO ... SELECT .....syntax - no VALUESkeyword involved:
您需要使用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
回答by AdamV
You were close! As long as C1, C2 and C3 are the same data types this should work.
你很接近！只要 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
回答by NAGESH
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;
