Insert specified columns from multiple tables into one table, using WHILE loop

Question

I've got 3 tables. I have to pick several specified columns from two of these tables, and insert them to one table. However, when I tried to "marge" them into one, it won't go as I expected.

[TableA]

Col_A    Col_B    Col_C    Col_D ...
  101       10       15      AAA
  102       20       25      BBB

[TableB]

Col_D    Col_E    Col_F    Col_G ...
  201       30       35      CCC
  202       40       45      DDD

[TableC] ('Target' table )

   Column_01             Column_02            Column_03 ...
TableA.Col_A-Row1     TableA.Col_B-Row1            0
       0                     0            TableB.Col_E-Row_1 ...
TableA.Col_A-Row2     TableA.Col_B-Row2            0
       0                     0            TableB.Col_E-Row_2 ...
       0                     0            TableB.Col_E-Row_3 ...
       0                     0            TableB.Col_E-Row_4 ...

I need to insert some columns (not all of them) from TableA and TableB to TableC, and when inserting TableA columns, the default value (in this case, 0) must be inserted into the rest of the columns. That's the same for TableB's case. One of the cunning point of these insert operation is, that TableA has the 1toN relation to TableB's records.

Now I wanted to achieve it by the simple query, like this:

INSERT INTO TableC (
  Column_01, Column_02, Column_03 ...
)
SELECT 
  TableA.Col_A, Table_A.Col_B, 0 ...
FROM TableA

But I also have heard that I can use Cursor to iterate TableB within the loop of TableA...

Is there any way to solve this? I have read several TSQL and SQL Server programming books, but none of them were describing such 'a query for badly-designed tables', just simple SQL basics.

I know it's not so complicated question, but I'm new to the Stored Procedure and TSQL, and I don't know where to start.

Thanks.

Rahul Rahul · Accepted Answer · 2015-05-26T21:35:21

Using cursor probably would be a bad idea. Since there is no matching column (no relation) between tableA and tableB; you can probably do a UNION along with insert into select from like

INSERT INTO TableC (
  Column_01, Column_02, Column_03 ...
)
SELECT 
  TableA.Col_A, Table_A.Col_B, 0 ...
FROM TableA
UNION
SELECT 0, 0, TableB.Col_D, TableB.Col_E, ...
FROM TableB

Insert specified columns from multiple tables into one table, using WHILE loop

1 Answers