I just make an script called: SP GENERATE UPDATES, it would seemed to sp_generate_inserts that i've been using a long time ago. It will generate updates statements from a sql server table.
-- SP_GENERATE_UPDATES USAGE:
--EXEC sp_generate_updates 'Table_for_updates', ''
-- Note: If the id_list variable is empty, it will generate a update statement for all rows
--EXEC sp_generate_updates 'Table_for_updates', '123,124,125'
-- NOTE: This example generate 3 update querys for this id of the primary key column
-- ADVISE: it's Only admit one primary key in the table
-- By MJM
FYI:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_generate_updates] (@table_name varchar(256), @id_list varchar(max))
as
begin
-- SP_GENERATE_UPDATES USAGE:
--EXEC sp_generate_updates 'Table_for_updates', ''
-- Note: If the id_list variable is empty, it will generate a update statement for all rows
--EXEC sp_generate_updates 'Table_for_updates', '123,124,125'
-- NOTE: This example generate 3 update querys for this id of the primary key column
-- ADVISE: it's Only admit one primary key in the table
-- By MJM
declare @PK_COLUMN_NAME varchar(256)
declare @PK_COLUMN_VALUE int
declare @COLUMN_NAME varchar(512)
declare @GET_PK_COLUMN cursor
declare @GET_COLUMNS cursor
declare @QUERY nvarchar(500)
declare @QUERY2 nvarchar(500)
declare @GET_ROWS_CURSOR CURSOR
declare @sep varchar(3)
declare @var varchar(max)
declare @UPDATE_QUERY varchar(max)
select @PK_COLUMN_NAME=column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_name = @table_name
print 'COLUMN NAME PK: ' + @PK_COLUMN_NAME
if (@PK_COLUMN_NAME IS NULL)
BEGIN
print 'You must define a primary key for ' + @table_name
END
if (@id_list <> '')
begin
set @QUERY = 'select ' + @PK_COLUMN_NAME + ' from ' + @table_name + ' where ' + @PK_COLUMN_NAME + ' in (' + @id_list + ')'
end
else
begin
set @QUERY = 'select ' + @PK_COLUMN_NAME + ' from ' + @table_name
end
set @QUERY = 'set @GET_ROWS_CURSOR = CURSOR FOR ' + @QUERY + '; OPEN @GET_ROWS_CURSOR'
exec sp_executesql@QUERY, N'@GET_ROWS_CURSOR CURSOR OUTPUT', @GET_ROWS_CURSOR OUTPUT
FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
set @GET_COLUMNS = CURSOR
FOR select column_name from information_schema.columns where table_name = @table_name
open @GET_COLUMNS
fetch next from @GET_COLUMNS into @COLUMN_NAME
set @sep = ''
set @UPDATE_QUERY = ''
WHILE @@FETCH_STATUS = 0
BEGIN
if (@COLUMN_NAME <> @PK_COLUMN_NAME)
BEGIN
set @QUERY2 = 'select @var=cast(' + @COLUMN_NAME + ' as varchar(max)) from '+@table_name+' where '+ @PK_COLUMN_NAME + ' = ' + CAST(@PK_COLUMN_VALUE as varchar(9))
exec sp_executesql
@QUERY2,
N'@var varchar(max) OUTPUT',
@var OUTPUT
if (@var IS NOT NULL)
begin
set @UPDATE_QUERY = @UPDATE_QUERY + @sep + @COLUMN_NAME + '=''' + replace(@var, '''', '''''') + ''''
set @sep = ', '
end
END
fetch next from @GET_COLUMNS into @COLUMN_NAME
END
CLOSE @GET_COLUMNS
DEALLOCATE @GET_COLUMNS
print 'update ' + @table_name + ' set ' + @UPDATE_QUERY + ' where ' + @PK_COLUMN_NAME + ' = ' + CAST(@PK_COLUMN_VALUE as varchar(9))
FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
END
CLOSE @GET_ROWS_CURSOR
DEALLOCATE @GET_ROWS_CURSOR
end
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------