Rara Rara - 2 months ago 8
SQL Question

Need to create stored procedure to copy data from multiple tables to one table

I only have experience in creating simple custom tables and I haven't done stored procedures before. I would like to know how to create a stored procedure to copy data from multiple tables into one single table.

There are four tables (and a total of 6 columns in these 4 tables) from which I have to copy data to my Main table.

Table 1 - C1, C2 = Main table - C1, C2
Table 2 - C1, C2 = Main table - C3, C4
Table 3 - C1, C2 = Main table - C5, C6
Table 4 - C1, C2 = Main table - C7, C8


This way my Main table will have a total of 8 columns with data from other tables, and 4 columns with time-stamp, serial number, and such.

What would be the best way to approach this?

Thanks in advance for taking the time to read and answer!

-Rara

Answer

You can try something like that (the columns Ci should be nullable):

CREATE PROCEDURE PROC_COPYTABLE
AS
BEGIN
    INSERT INTO MAIN(C1, C2) SELECT C1, C2 FROM T1 
    INSERT INTO MAIN(C3, C4) SELECT C1 as C3, C2 as C4 FROM T2
    INSERT INTO MAIN(C5, C6) SELECT C1 as C5, C2 as C6 FROM T3
    INSERT INTO MAIN(C7, C8) SELECT C1 as C7, C2 as C8 FROM T4 
END
Comments