Stored Procedure to Insert Data from one table to

问题:

I have one master table and few smaller table.

• Master table has C1 | C2 | C3 | C4 | C5 |
• A small table has C1 | C2 | C3 |

Also @C1 (a variable that has a value which matches the value of C1 in Master table.

The column names matches for both table. I want to create a stored procedure which inserts values from Master table (C1, C2, and C3) to smaller table (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

回答1:

You need to use the INSERT INTO ... SELECT ..... syntax - no VALUES keyword involved:

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

回答2:

You were close! As long as C1, C2 and C3 are the same data types this should work.

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

回答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;