I'm using SQL server and I'm trying to create a script to update my table if row exists, else insert into that table.
Let's call the table I want to add rows into SALES and my other PROJECT
Will always be a one-one relationship.
If a new project comes in, I want to create a SQL statement that checks if that project exists, if it does it should update a few columns of my sales table, but if it does not exists then add that record into my SALES table.
I have ProjectID, Client and Status in both tables.
I have tried something like this, but not gotten it right.
Expand|Select|Wrap|Line Numbers
- IF EXISTS (SELECT 1 FROM SALES INNER JOIN PROJECT
- ON (SALES.ProjectID = PROJECT.ProjectID
- AND
- SALES.Status = PROJECT.Status))
- BEGIN
- UPDATE SALES(ProjectID, Client, Status)
- END
- ELSE
- BEGIN
- INSERT INTO SALES(ProjectID, Client, Status)
- Select ProjectID, Client, Status
- from PROJECT
- Where Status = 'Active' or Status = 'Expected' or ProjectType = 'Confirmed'
- END
Thank you very much!