Clash Royale CLAN TAG#URR8PPP

How to in insert new rows from one table to another based on date?

I am having two tables (table1 and table2). I have columns Date, A, B, C in table1 and columns Date, D, E in table2. I need to transfer column D and E from table2 to table1 based on Date in both the tables. Can anyone please help

Date, A, B, C

Date, D, E

Tried below code but getting 'multi-part identifier "table1.Date" could not be bound.' error

INSERT INTO table1
SELECT D,E FROM table2
WHERE table2.Date = table1.Date


Table1 :
Date A B C
1945-01-01 1 2 3
1945-02-01 1 2 4
1945-03-01 5 6 7

Table2 :
Date D E
1945-02-01 8 2
1945-03-01 5 6


Expected output:

Table1 :
Date A B C D E
1945-01-01 1 2 3 Null Null
1945-02-01 1 2 4 8 2
1945-03-01 5 6 7 5 6


INSERT INTO table1 select * from (SELECT D,E FROM table2, tabel1 WHERE table2.Date = table1.Date) t21

[Date]

2 Answers
2

First you have to add those columns to Table1. Then you need to update the existing rows. Something like this should work.

alter table Table1
add D int

alter table Table1
add E int

GO

update t
set D = t2.D
, E = t2.E
from Table2 t2
left join Table1 t on t.Date = t2.Date


You are rather asking of how to JOIN tables, because you can not add columns with insert statement.

JOIN

Having said that, you are looking for LEFT JOIN (well, if table on the left side of operator is Table1), try this:

LEFT JOIN

Table1

SELECT * FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.[Date] = T2.[Date]


If you want to have this as "table" and use it without JOINs, then I recommend you getting familiar with views: CREATE VIEW (Transact-SQL)

JOIN

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.