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?
1
-
I need to auto generate the script complete with all the data inside the table. I have one table with the correct data and another table in another system with wrong data in the table. What I need to do is auto generate an update script that will allow me to update the wrong table with the data from the correct table.Rabin– Rabin2010-07-07 04:10:25 +00:00Commented Jul 7, 2010 at 4:10
Add a comment
|
5 Answers
There's a nice free tool here http://www.lss.co.uk/Products/LiveDataScript/
3 Comments
Mike Goatly
+1, because I wrote it :) - the site is down now, but I'll try to make it available on my blog (when I finally manage to get that back up and running...)
Mike Goatly
The link for LiveDataScript is now here: goatly.net/downloads/livedatascript
Piers Myers
This tool only creates
INSERT scripts, not UPDATE ones as the OP asked for. I also need a tool to create UPDATE scripts.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.
answered Jul 6, 2010 at 11:42
Stefan Steinegger
64.8k17 gold badges132 silver badges193 bronze badges
Comments
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.
Comments
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
Comments
SELECT
'UPDATE [Table] SET field1 = ' + field1 + ' , field2 = ' + field2 + ' , field3 = ' + field3 + ' WHERE <condition> ' FROM <Table>
Use extra single Quotes wherever string data is updated.
