Doing a data compare with T-SQL in a query in SQL Server 2005

Question

Hi! Wondering if anyone knows an easy way to do this. I have a couple of tables with data, this data is "static" in that way that users don't change it, it's only me that gets to insert/update rows. And I wonder if there's an easy way to insert rows that don't exists and update rows that are different.

The way that I figured would work would be to do like this:

IF((SELECT COUNT(*) FROM myTable WHERE myId = x) = 0)
   INSERT myTable() values()
ELSE
   UPDATE myTable SET data = updatedData WHERE myId = 0

But since I have quite many rows that I want to update, I wonder if there's an easier way to do this. There's a Data comparer in VS Database, is it possible to use it in some way from a query script or some other way? I want these updates to be done when deploying a database project with VS.

Thanks.

Answer 1

You can make a trigger on datbase which automatically update/insert rows.

Answer 2

You can remove the if statement easily, like so:

insert into myTable
select
   <values>
where
   not exists(select * from myTable where myId = @x)

Then, you could check the @@rowcount value. If it is non-zero, then you can exit that section of logic, knowing the record was entered into the database.

If @@rowcount is zero, it means that the record with that id exists in the database and you can issue the update:

update 
    myTable 
set 
    <values>
where 
    myId = @x

Note that you want to make sure this runs within a transaction, as you could have race conditions that occur if you are trying to update this code from two separate clients.

Answer 3

What you're after is the SQL Server 2008 'MERGE' command. Here's the MS documentation page http://technet.microsoft.com/en-us/library/bb510625.aspx.

Snippet:

MERGE INTO dbo.myTable AS Target
USING (VALUES   ('something 1','other 1'), 
                ('something 2', 'other 2'), 
                ('something 3', 'other 3'))
       AS Source (Field1, Field2)
ON Target.myId = Source.myId
WHEN MATCHED THEN
    UPDATE SET Field1 = Source.Field1,
               Field2 = Source.Field2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Field1, Field2) 
    VALUES (Field1, Field2)

I'm guessing from the SQL2005 tag that this isn't going to help you much (sorry!), so casperOne's answer is probably the most suitable.