Scripting specific records in SQL Server

Question

This is probably a bit limited, but valuable scenario. I have a SQL Server 2008 database with a table with millions of records. There seems to be some problem with multiple entries. I am trying to reproduce the problem. In an attempt to do this, I finally got an offensive post id. I would like to create an INSERT statement associated with this single record in my PROD database. Then I can easily transfer it to the TESTING database to reproduce and solve the problem.

Basically, I need to generate one INSERT statement for one record from one table, where I know the primary key value for the record.

Does anyone have any ideas how I can do this? Essentially, I want to generate insert statements conditionally.

Thanks!

Answer 1

First, try to recreate what you want to insert using the SELECT .

After that, you can insert into the table with INSERT INTO as follows:

 INSERT INTO tablename SELECT .... 

---

If they are on different servers, you can use INSERT as follows:

 INSERT INTO tablename VALUES (...) 

using the values ​​specified by SELECT on another server, fill in the insertion values.

Answer 2

In your specific case, I think you can do this:

 CREATE PROCEDURE dbo.GenerateSingleInsert @table NVARCHAR(511), -- expects schema.table notation @pk_column SYSNAME, -- column that is primary key @pk_value INT -- change data type accordingly AS BEGIN SET NOCOUNT ON; DECLARE @cols NVARCHAR(MAX), @vals NVARCHAR(MAX), @valOut NVARCHAR(MAX), @valSQL NVARCHAR(MAX); SELECT @cols = N'', @vals = N''; SELECT @cols = @cols + ',' + QUOTENAME(name), @vals = @vals + ' + ' + REPLICATE(CHAR(39),3) + ',' + REPLICATE(CHAR(39),3) + ' + ' + REPLICATE(CHAR(39),2) + '+' + 'RTRIM(' + CASE WHEN system_type_id IN (40,41,42,43,58,61) THEN 'CONVERT(CHAR(8), ' + QUOTENAME(name) + ', 112) + '' '' + CONVERT(CHAR(14), ' + QUOTENAME(name) + ', 14)' ELSE 'REPLACE(' + QUOTENAME(name) + ','''''''','''''''''''')' END + ') + ' + REPLICATE(CHAR(39),2) FROM sys.columns WHERE [object_id] = OBJECT_ID(@table) AND system_type_id <> 189 -- can't insert rowversion AND is_computed = 0; -- can't insert computed columns SELECT @cols = STUFF(@cols, 1, 1, ''), @vals = REPLICATE(CHAR(39), 4) + ' + ' + STUFF(@vals, 1, 13, '') + REPLICATE(CHAR(39), 2); SELECT @valSQL = N'SELECT @valOut = ' + @vals + ' FROM ' + @table + ' WHERE ' + QUOTENAME(@pk_column) + ' = ''' + RTRIM(@pk_value) + ''';'; EXEC sp_executesql @valSQL, N'@valOut NVARCHAR(MAX) OUTPUT', @valOut OUTPUT; SELECT SQL = 'INSERT ' + @table + '(' + @cols + ') SELECT ' + @valOut; END GO 

So try:

 CREATE TABLE dbo.splunge ( ID INT, dt DATETIME, rv ROWVERSION, t NVARCHAR(MAX) ); INSERT dbo.splunge(ID, dt, t) SELECT 1, GETDATE(), 'foo' UNION ALL SELECT 2, GETDATE(), 'bar' UNION ALL SELECT 3, GETDATE(), 'O''Brien'; EXEC dbo.GenerateSingleInsert N'dbo.splunge', N'ID', 1; SQL ------------- INSERT dbo.splunge([ID],[dt],[t]) SELECT '1','20120517 10:07:07:330','foo' EXEC dbo.GenerateSingleInsert N'dbo.splunge', N'ID', 2; SQL ------------- INSERT dbo.splunge([ID],[dt],[t]) SELECT '2','20120517 10:07:07:330','bar' EXEC dbo.GenerateSingleInsert N'dbo.splunge', N'ID', 3; SQL ------------- INSERT dbo.splunge([ID],[dt],[t]) SELECT '3','20120517 10:07:07:330','O''Brien' 

If there is an IDENTITY column, you may need to set SET IDENTITY_INSERT ON for the TEST table and make sure there is no collision. Probably about 500 caveats I should mention, I have not tested all data types, etc.

However, in a more general case, this is much more than that. Weiss K has a fairly robust stored procedure that should demonstrate how difficult it is to do this:

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

You are probably much better off using a tool like Red-Gate SQL Data Compare to select a specific row and create an insert for you. As I wrote about , paying for a tool is not only money, but also hours of troubleshooting and error fixing that someone else has already done for you.

Answer 3

Aaron, I liked your code, it solved the problem for me. I ran into several problems using it (as you would say, I would) with zeros and text type, so I made some changes to solve these problems.

 ALTER PROCEDURE dbo.GenerateSingleInsert @table NVARCHAR(511), -- expects schema.table notation @pk_column SYSNAME, -- column that is primary key @pk_value INT -- change data type accordingly AS BEGIN SET NOCOUNT ON; DECLARE @cols NVARCHAR(MAX), @vals NVARCHAR(MAX), @valOut NVARCHAR(MAX), @valSQL NVARCHAR(MAX); SELECT @cols = N'', @vals = N''; SELECT @cols = @cols + ',' + QUOTENAME(name), @vals = @vals + ' + '','' + ' + 'ISNULL('+REPLICATE(CHAR(39),4)+'+RTRIM(' + CASE WHEN system_type_id IN (40,41,42,43,58,61) -- datetime types THEN 'CONVERT(CHAR(8), ' + QUOTENAME(name) + ', 112) + '' ''+ CONVERT(CHAR(14), ' + QUOTENAME(name) + ', 14)' WHEN system_type_id IN (35) -- text type NOTE: can overflow THEN 'REPLACE(CAST(' + QUOTENAME(name) + 'as nvarchar(MAX)),'+REPLICATE(CHAR(39),4)+','+REPLICATE(CHAR(39),6)+')' ELSE 'REPLACE(' + QUOTENAME(name) + ','+REPLICATE(CHAR(39),4)+','+REPLICATE(CHAR(39),6)+')' END + ')+' + REPLICATE(CHAR(39),4) + ',''null'') + ' FROM sys.columns WHERE [object_id] = OBJECT_ID(@table) AND system_type_id <> 189 -- can't insert rowversion AND is_computed = 0; -- can't insert computed columns SELECT @cols = STUFF(@cols, 1, 1, ''), @vals = REPLICATE(CHAR(39),2) + STUFF(@vals, 1, 6, '') + REPLICATE(CHAR(39),2) ; SELECT @valSQL = N'SELECT @valOut = ' + @vals + ' FROM ' + @table + ' WHERE ' + QUOTENAME(@pk_column) + ' = ''' + RTRIM(@pk_value) + ''';'; EXEC sp_executesql @valSQL, N'@valOut NVARCHAR(MAX) OUTPUT', @valOut OUTPUT; SELECT SQL = 'INSERT ' + @table + '(' + @cols + ') SELECT ' + @valOut; END