T-SQL INSERT INTO with LEFT JOIN

Question

Sorry this is a syntax question, but in T-SQL how do I specify in INSERT statement in which DB I want to INSERT and in LEFT JOIN from which DB I want to join?

if not exists (select * from [DB_A].[dbo.a_test])
create table [DB_A].[dbo.a_test] (
a int(10) ,
b int(10) ,
c varchar(200) ,
d varchar(200) ,
e varchar(200) ,            
PRIMARY KEY (a)
)


INSERT INTO [DB_A].[dbo.a_test] (a,b,c, d)
VALUES dbo.products.product_info, dbo.products.product_date, dbo.products.smth, *dbo.program.program_name*, dbo.program.program_smth
FROM [DB_B].dbo.products    
LEFT JOIN [DB_B].dbo.program
ON dbo.program.program_name = dbo.products.product_info

Sorry for such a noob question, but I could not find a suitable example.

Your syntax of creating table is incorrect. There is no `int(10)` datatype in MS SQL Server. — Hamlet Hakobyan, May 22 '14 at 13:30

score 50 · Accepted Answer · edited Oct 21 '16 at 07:42

50

You want insert into . . . select:

INSERT INTO [DB_A].[dbo.a_test](a,b,c,d,e) --ADDED A COLUMN
    select p.product_info, p.product_date, p.smth, pr.program_name, pr.program_smth
    FROM [DB_B].dbo.products p LEFT JOIN
         [DB_B].dbo.program pr
         ON p.program_name = pr.product_info;

I also fixed the query to use table aliases, so it is much easier to read.

edited Oct 21 '16 at 07:42

Hemal

3,682
1
23
54

answered May 22 '14 at 13:30

Gordon Linoff

1,242,037
58
646
786

Number of columns in INSERT statement and SELECT statement do not match. – Hemal Oct 21 '16 at 04:12

score 5 · Answer 2 · answered May 22 '14 at 13:29

INSERT INTO [DB_A].[dbo.a_test] (a,b,c, d)
SELECT dbo.products.product_info, dbo.products.product_date, dbo.products.smth, dbo.program.program_smth
FROM [DB_B].dbo.products    
LEFT JOIN [DB_B].dbo.program
ON dbo.program.program_name = dbo.products.product_info

T-SQL INSERT INTO with LEFT JOIN

2 Answers2

Linked