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$
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.
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.
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.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With