Insert record from multiple tables into another in SQL Server

Last Reply 20 days ago By pandeyism

Posted 21 days ago

I have 1 table: 60 fields

Addtional 10 more tables in MS SQL (6 fields each of 10 table)

How can I insert 10 tables (total 60 fields) in to 1 table already has 60 fields

Posted 20 days ago

Hi KatieNgoc,

Refer below sample query.

SQL

DECLARE @tableMain AS TABLE(Id INT, Name VARCHAR(20), Country VARCHAR(20),PostalCode INT)
DECLARE @table1 AS TABLE(Id INT,ID_P INT)
DECLARE @table2 AS TABLE(Name VARCHAR(20),ID_P INT)
DECLARE @table3 AS TABLE(Country VARCHAR(20),ID_P INT)
DECLARE @table4 AS TABLE(PostalCode INT,ID_P INT)

INSERT INTO @table1 VALUES(1,1)
INSERT INTO @table1 VALUES(2,2)
INSERT INTO @table2 VALUES('Mudassar Khan',1)
INSERT INTO @table2 VALUES('John Hammod',2)
INSERT INTO @table3 VALUES('India',1)
INSERT INTO @table3 VALUES('USA',2)
INSERT INTO @table4 VALUES('111111',1)
INSERT INTO @table4 VALUES('222222',2)

INSERT INTO @tableMain (Id,Name,Country,PostalCode)
SELECT Id,Name,Country,PostalCode
FROM @table1 t1
INNER JOIN @table2 t2 ON t1.ID_P = t2.ID_P
INNER JOIN @table3 t3 ON t1.ID_P = t3.ID_P
INNER JOIN @table4 t4 ON t2.ID_P = t4.ID_P
SELECT * FROM @tableMain