Creating an UPDATE script in SQL Server 2005

Question

I have a table in SQL Server 2005 filled with data. Is there a method by which I can generate update statements including the data in it?

Answer 1

If you don't find any better solution, try this:

SELECT 
  'UPDATE [Table] (field1, field2, field3) Values (' 
     + field1 + ', ' 
     + field2 + ', ' 
     + field3 + ')'
FROM [Table]

I know, not nice.

Answer 2

Go here and get the Microsoft SQL Server Management Studio Express SSMSE Download page

After installing you can then connect to your database with the tool and generate several types of "vanilla" scripts.

Answer 3

If you mean data on the same row, just use it in the Update Statement

Update MyTable Set ColumnA = ColumnB + ColumnC

If you want to use data from other rows, you probably have to join it back to it's self

Update a 
Set ColumnA = b.ColumnD
From MyTable a
Join MyTable b on a.ColumnB = b.ColumnC

Answer 4

SELECT  
  'UPDATE [Table] SET field1 = ' + field1  + ' , field2 = ' + field2 + ' , field3 = ' + field3 + ' WHERE <condition> ' FROM <Table>

Use extra single Quotes wherever string data is updated.

Answer 5

There's a nice free tool here http://www.lss.co.uk/Products/LiveDataScript/