T-Sql Insert into with Left Join

T-SQL INSERT INTO with LEFT JOIN

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.

Insert using LEFT JOIN and INNER JOIN

You have to be specific about the columns you are selecting. If your user table had four columns id, name, username, opted_in you must select exactly those four columns from the query. The syntax looks like:

INSERT INTO user (id, name, username, opted_in)
SELECT id, name, username, opted_in 
FROM user 
LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id

However, there does not appear to be any reason to join against user_permission here, since none of the columns from that table would be inserted into user. In fact, this INSERT seems bound to fail with primary key uniqueness violations.

MySQL does not support inserts into multiple tables at the same time. You either need to perform two INSERT statements in your code, using the last insert id from the first query, or create an AFTER INSERT trigger on the primary table.

INSERT INTO user (name, username, email, opted_in) VALUES ('a','b','c',0);
/* Gets the id of the new row and inserts into the other table */
INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(), 4)

Or using a trigger:

CREATE TRIGGER creat_perms AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_permission (user_id, permission_id) VALUES (NEW.id, 4)
END

How to use INSERT INTO with a LEFT JOIN?

Instead of giving the column name directly, please specify the alias name to say from which table the column should take. May be here both tables having same column the you are trying to select. You should specify the exact table

INSERT INTO [TABLE_1] (USD,EUR,RUR)

SELECT [T1/T2].USD,[T1/T2].EUR,[T1/T2].RUR 

FROM TABLE_1 AS T1 LEFT JOIN TABLE_2 AS T2
   ON T1.[DATE] = T2.[DATE1]

Either you can specify T1 or T2 as per your business logic. Please rewrite the query as mentioned here. This will solve the problem. Please try this.

INSERT INTO SELECT with a LEFT JOIN to prevent duplicates, only prevents duplicates already in the table

You are correct on the "snapshot" point: any insertions into table1 in this query will not affect the LEFT JOIN table1.

But you would still need a DISTINCT to guarantee uniqueness from the queried data.

INSERT INTO table1 
SELECT DISTINCT
       t2.col1, 
       t2.col2 
FROM   table2 t2 
       LEFT JOIN table1 t1 
         ON t2.col1 = t1.col1 
            AND t2.col2 = t1.col2 
WHERE  t1.col1 IS NULL 

---

However:

• LEFT JOIN is a poor man's replacement for NOT EXISTS and EXCEPT which the optimizer understands much better
• You should always specify column names in an INSERT

So your code should look like one of these options:

INSERT INTO table1 (col1, col2)
SELECT DISTINCT
       t2.col1, 
       t2.col2 
FROM   table2 t2 
WHERE NOT EXISTS (SELECT 1
       FROM table1 t1 
         WHERE t2.col1 = t1.col1 
            AND t2.col2 = t1.col2);

INSERT INTO table1 (col1, col2)
SELECT DISTINCT
       t2.col1, 
       t2.col2 
FROM   table2 t2 
WHERE NOT EXISTS (                    -- or you can use EXISTS/EXCEPT
       SELECT t2.col1, t2.col2
       INTERSECT
       SELECT t1.col1, t1.col2
       FROM table1 t1);

INSERT INTO table1 (col1, col2)
SELECT                  -- EXCEPT implies DISTINCT
       t2.col1, 
       t2.col2 
FROM   table2 t2
EXCEPT
SELECT t1.col1, t1.col2
FROM table1 t1;

How to use insert into and join together

I suggest you use EXCEPT

Example:

INSERT INTO T1( [Date], Details )
SELECT [Date], Details
FROM T2
EXCEPT -- This is equivalent to subtracting T1 records from T2 records
SELECT [Date], Details
FROM T1

You can also do it with a LEFT JOIN:

INSERT INTO T1( [Date], Details )
SELECT [Date], Details
FROM T2
    -- Add all of the columns that you want to match one.
    LEFT JOIN T1 ON T2.[Date] = T1.[Date] AND T2.Details = T1.Details
-- This ensures that only records that did not match T1 are returned.
WHERE T1.[Date] IS NULL

Left Join method is useful when you want to return additional columns (in the SELECT column list) on top of the columns you are matching tables on.

Except method is better suited if you want to filter tables on all of their output columns.

Insert into become very slow after adding a left join. What could be the problem?

I don't know why adding second rows from t4 slows makes things slow and without data access and execution plans we can't do much, I suppose.

But there is one thing you could try. You are using the same code here, so move it to with clause, then join this part twice into your main query using rn = 1 and rn = 2. Simplified code, without t3 and t5:

with t4 as (
    select * 
      from (select col1,col2,col3,col4,col5,
                   row_number() over (partition by col1 order by col2 desc) rn
              from tb4 )
      where rn <= 2 )
select * 
    from tb1 t1                                               
    left join tb2 t2 on t2.col1 = t1.col1                     
    left join t4 t4a on t4a.col1 = t2.col2 and t4a.rn = 1    
    left join t4 t4b on t4b.col1 = t2.col2 and t4a.rn = 2    

---

Related Topics