0

I am trying to change my current query to say, if x exists, then update values, otherwise just insert into table.

Right now my query is something like

with t1 as
(
    select * from table
)
insert into existingTable

How do I add an IF EXISTS, then UPDATE values, if not INSERT INTO.

I am trying not to have the WITH statement twice because it is very long.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
solarissf
  • 1,199
  • 2
  • 23
  • 58
  • 1
    Use a SQL Merge statement: https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ – Brad Jun 07 '18 at 19:22
  • 2
    Maybe take a look at MERGE? https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 – Sean Lange Jun 07 '18 at 19:22
  • 1
    Also, don't get hung up on a CTE for this. There are other ways. I don't see the value in using it since you aren't going to update the base tables through the CTE itself i don't suspect... given you also want an insert. Perhaps a temp table or some other logic would be better. can you post the actual CTE code? – S3S Jun 07 '18 at 19:33
  • Have you ever heard of `MERGE`?? – Eric Jun 07 '18 at 19:35
  • thanks... I didn't know about MERGE. looks promising. also @scsimon, you are correct, probably don't need to use cte, a temptable might work just fine. let me try that. thanks! – solarissf Jun 07 '18 at 19:36
  • The question was specific to using it with at CTE @TabAlleman – S3S Jun 07 '18 at 19:48
  • @scsimon the solution in the duplicate can be used with or without a CTE. You yourself said "don't get hung up on a CTE for this". It really doesn't change anything whether a CTE is involved or not. – Tab Alleman Jun 07 '18 at 19:50
  • Yes, I said you may not need to use a CTE, but can you show me how you can run an `IF EXISTS UPDATE ELSE INSERT` following a `CTE` @TabAlleman ? – S3S Jun 07 '18 at 19:55
  • I see what you mean. For that part of it, here is the duplicate: https://stackoverflow.com/questions/39576785/using-if-exists-with-a-cte Instead of IF, you have to `UPDATE WHERE EXISTS()` and `INSERT WHERE NOT EXISTS()` But a question that combines two duplicate questions isn't a new question. – Tab Alleman Jun 07 '18 at 20:05
  • I disagree with your last comment because it conflicts with the definition of duplicate but that is just my two cents. I feel if that was the case 2/3 of the questions on stack overflow could be removed @TabAlleman – S3S Jun 07 '18 at 22:16
  • basically based on the replies on this thread I was able to solve my issue. I previously saw those other threads and I couldn't figure it out.... so this question helped me! thanks!! – solarissf Jun 08 '18 at 12:16

0 Answers0