Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL INSERT missing rows from Table A to Table B

I'm trying to insert rows into table 'Data' if they don't already exist.

For each row in Export$, I need the code to check 'Data' for rows that match both Period (date) and an ID (int) - if the rows don't already exist then they should be created.

I'm pretty sure my 'NOT EXISTS' part is wrong - what's the best way to do this? Thanks for all your help

    IF NOT EXISTS (SELECT * FROM Data, Export$ WHERE Data.ID = Export$.ID AND Data.Period = Export$.Period)
    INSERT INTO Data (Period, Performance, ID)
    SELECT Period, [Return], [ID] FROM Export$
like image 869
Pat M Avatar asked Feb 12 '13 23:02

Pat M


People also ask

How do you insert a row in a table between two existing rows in SQL?

To insert a row into a table, you need to specify three things: First, the table, which you want to insert a new row, in the INSERT INTO clause. Second, a comma-separated list of columns in the table surrounded by parentheses. Third, a comma-separated list of values surrounded by parentheses in the VALUES clause.

Does insert statement lock the table?

The insert and update statements are supposed to create row-level locks. However, when the number of locks in any transaction is 5,000 or more then a lock escalation occurs and it creates a table level lock.

How can you insert rows in table in SQL?

If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.

Can we give where clause in insert query?

You Should not use where condition in Insert statement. If you want to do, use insert in a update statement and then update a existing record.


1 Answers

try this:

INSERT INTO Data (Period, Performance, ID)
SELECT Period, [Return], [ID] 
FROM Export$ e
where not exists (
select *
from Data
where  ID = e.ID and Period = e.Period)
like image 70
pjacko Avatar answered Sep 29 '22 04:09

pjacko