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"
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.