Table1:

Table2:

Table3:

Table4:

I have two tables('Table2','Table3') I'm trying to join these two tables and insert into Table4 using Table1.

I have tried:

INSERT INTO table4 
    SELECT DISTINCT 
        COALESCE(tt.ID, t1.ID) AS ID,
        COALESCE(tt.Name, t1.Name) AS Name,
        COALESCE(tt.Class, t1.Class) AS Class,
        tt.Date, tt.Intime, tt.Outtime, tt.INAM, tt.OUTPM   
    FROM 
        table1 AS t1
    CROSS JOIN 
        (SELECT * FROM Table4  
         UNION ALL
         SELECT ID, Name, Class, Indate AS Date, Intime, NULL, INAM, NULL 
         FROM Table2) AS tt
    CROSS JOIN 
        (SELECT * FROM Table4  
         UNION ALL
         SELECT ID, Name, Class, Outdate AS Date, NULL, Outtime, NULL, OUTPM 
         FROM Table3) AS tt

SELECT * FROM Table4

Please someone help me two join Table2 and Table3 and insert into Table4 using Table1. Thank you...

CodePudding user response：

Try this code
I start by create tables for your testdata

USE [tempdb]

IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
CREATE TABLE table1 (ID int, Name varchar(50), Class varchar(20))
INSERT INTO table1 VALUES (1, 'Paul', '1st')

IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
CREATE TABLE table2 (ID int, Name varchar(50), Class varchar(20), Date date, Intime time, INAM varchar(20))
INSERT INTO table2 VALUES (1, 'Paul', '1st', '2022-12-06', '8:30AM', 'P')

IF OBJECT_ID('table3') IS NOT NULL DROP TABLE table3
CREATE TABLE table3 (ID int, Name varchar(50), Class varchar(20), Date date, Outtime time, OUTPM varchar(20))
INSERT INTO table3 VALUES (1, 'Paul', '1st', '2022-12-06', '4:30PM', 'P')

IF OBJECT_ID('table4') IS NOT NULL DROP TABLE table4
CREATE TABLE Table4 (ID int, Name varchar(50), Class varchar(20), Date date, Intime time, Outtime time, INAM varchar(20), OUTPM varchar(20))

SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM table3

INSERT INTO table4
SELECT t1.[ID], t1.[Name], t1.[Class], t2.[Date], t2.[Intime], NULL, t2.[INAM], NULL FROM table2 AS t2 JOIN table1 AS t1 ON t1.[ID] = t2.[ID]
UNION ALL
SELECT t1.[ID], t1.[Name], t1.[Class], t2.[Date], NULL, t2.[Outtime] , NULL, t2.[OUTPM] FROM table3 AS t2 JOIN table1 AS t1 ON t1.[ID] = t2.[ID]

SELECT * FROM table4

CodePudding user response：

You almost certainly do not want a CROSS JOIN, you want an INNER JOIN. A CROSS JOIN gives you the Cartesian product, which is every possible combination of the rows. An INNER JOIN gives you the matching records.

Assuming that ID is a primary key in Table2 and Table3, then something like this:

INSERT INTO Table4 ([ID], [Name], [Class], [Date], [Intime], [Outtime],  [INAM], [OUTPM])
SELECT t2.[ID], y2.[Name], t2.[Class], t2.[Date], t2.[Intime], t3.[Outtime], t2.[INAM], t3.[OUTPM]
FROM Table2 AS t2 
INNER JOIN Table3 AS t3 ON t2.ID = t3.ID;

Your sample data is very limited, but what is shown is an INNER JOIN.