HoneyHoney

USE AdventureWorks2012;

DECLARE @personId int = 1

CREATE TABLE #tmp_person(
 BusinessEntityID int,
 PersonType nchar(2),
 NameStyle bit,
 Title nvarchar(8),
 FirstName nvarchar(50),
 MiddleName nvarchar(50),
 LastName nvarchar(50),
 Suffix nvarchar(10),
 EmailPromotion int,
 AdditionalContactInfo xml,
 Demographics xml,
 rowguid uniqueidentifier,
 ModifiedDate datetime);

CREATE TABLE #tmp_address(
 AddressID int,
 AddressLine1 nvarchar(60),
 AddressLine2 nvarchar(60),
 City nvarchar(30),
 StateProvinceID int,
 PostalCode nvarchar(15),
 SpatialLocation geography,
 rowguid uniqueidentifier,
 ModifiedDate datetime);


INSERT INTO #tmp_person
SELECT TOP 1 *
FROM Person.Person
WHERE BusinessEntityID = @personId

--delete data
--DELETE FROM Person.Person WHERE BusinessEntityID = @personId


PRINT 'ROLLBACK SCRIPT'
PRINT '----------------------------------------'

SET NOCOUNT ON

CREATE TABLE #tables (id int identity PRIMARY KEY, table_name varchar(50), tmp_table_name varchar(50))

--tables to insert the data back
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Person', '#tmp_person')
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Address', '#tmp_address')

DECLARE @columns NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tablename VARCHAR(50),
  @tmptablename VARCHAR(50),
  @rowcount int

------------------- AdventureWorks2012 -------------------
PRINT 'USE AdventureWorks2012;'

DECLARE table_cursor CURSOR FAST_FORWARD LOCAL
FOR
SELECT table_name, tmp_table_name
FROM #tables
ORDER BY id

OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tablename, @tmptablename

WHILE @@FETCH_STATUS = 0
 BEGIN

 --Check if data exists in temp table. Skip if no data
 SET @sql = N'SELECT 1 FROM ' + @tmptablename
 EXEC(@sql)

 SET @rowcount = @@rowcount

 IF @rowcount <> 0
 BEGIN
  -- get columns names to be inserted
  SELECT @columns = STUFF(
  (SELECT '+'''''', '''''' + ISNULL(CAST(' + name + ' AS varchar(2000)), ''NULL'')' FROM sys.columns c
   WHERE object_id = object_id(@tablename)
   ORDER BY c.column_id
   FOR XML PATH('')
  ), 1, 12, '')


  -- format INSERT script
  SET @sql = N'SELECT @sqlout = STUFF(
          (SELECT N''INSERT INTO ' + @tablename + ' VALUES ('''''' + ' + @columns + N' + N''''''); ''
          FROM ' + @tmptablename +'
          FOR XML PATH('''')
          ), 1, 0, '''')'

  EXEC sys.sp_executesql @sql, N'@sqlout NVARCHAR(MAX) OUTPUT',  @sqlout = @sql OUTPUT

  SET @sql = REPLACE(@sql, '''NULL''', 'NULL')
  SET @sql = REPLACE(@sql, 'INSERT INTO', CHAR(10)+'INSERT INTO')

  PRINT @sql

 END

 FETCH NEXT FROM table_cursor
 INTO @tablename, @tmptablename

 END

CLOSE table_cursor
DEALLOCATE table_cursor