1

I have a comma separated list of items that I retrieve from a table, the result is so

      declare @IDs = varchar(max) 
      set @IDs = '11,23,33,44,55'  -- this varies

From this onward what I want to do is to iterate through each one and compare to my table and see if that ID exists, If it exists update the row but if it does not add a row to my table.

Any help is appreciated.

NoviceDeveloper
  • 1,270
  • 3
  • 15
  • 41
  • 1
    Put Ids to table and then use `Merge` – Darka Jan 29 '15 at 14:26
  • @Darka If the ID exists for example if 11 Exists I want to update that row, but if it does not exist I want to add a row with 11 in that table. – NoviceDeveloper Jan 29 '15 at 14:28
  • I mean put Ids to some Temp table and then use [Merge](https://msdn.microsoft.com/en-us/library/bb510625.aspx) for insertion and update – Darka Jan 29 '15 at 14:29
  • Don't store more than one value in any field. Comma-separated strings look OK on the surface, but they create enormous amounts of headache later on. Put IDs into a separate table. – Sergey Kalinichenko Jan 29 '15 at 14:30
  • This is possible duplicate of a duplicate: http://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005 – InitK Jan 29 '15 at 14:30
  • You could probably shorten this process considerably and just do this as an update and skip the whole step of retrieving a list of values and then conditionally doing an update. Add the condition in the code that generates the list or even better just execute a single set based update statement and avoid all these steps. – Sean Lange Jan 29 '15 at 14:50

1 Answers1

1

You need something like this (a MERGE):

MERGE targetTableName AS TARGET
USING @tableOfIds AS SOURCE
ON ( TARGET.Id = SOURCE.Id )
WHEN MATCHED THEN
    UPDATE
    -- Here you will put your update statement. I mean SET...

where @tableOfIds is a table that will hold your ids, a table with only one column, let's call it Id.

Christos
  • 53,228
  • 8
  • 76
  • 108