Monday, July 13, 2009

Generating insert statements from any table of Sql Server 2005 with any data type

In my experience, I have came across deploying many systems, creating scripts for new tables, there was always a issue how to generate insert statements of a sql server table, previously in one of my company that I have worked for many years they use to have a tool for generating insert statements but now thanks to sp_executesql , it has make life easy for every support software engineer, here is a simple sample script to generate sql insert statements , the example is from a sample employee table containing two fields employee_code varchar(3) and employee_name varchar (50)

set quoted_identifier off

declare @sql1 nvarchar(1000)

set @sql1="select 'insert employee(employee_code, employee_name) values(''' + employee_code + ''',''' + employee_name + ''')' from employee"

print @sql1

exec sp_executesql @sql1;

Remember if you have a table with values with single quote or binary data then you have to change this script accordingly.