generating insert scripts

Hi,

I have a table with 25 columns and 100 rows of data. I want to generate Insert scripts with data. Sql 2005 does not provide any option out of the box to do so.

Has anyone tried this/ used any other tool for the same.

Please share the solution.

Thanks.

hi,
you can use a third party tool.. there's a lot of them.. or you just write a query something like

select 'insert into tblTable (col1, col2, col3.......)'
select 'select '''+ col1 + ''',''' + col2 + ''''.......... + ' union all' from tblTable

- clintz|||

you can use this script:

declare @.Columns varchar(5000),@.TableName varchar(50),@.Condition varchar(500), @.ColumnVal varchar(8000)
set @.TableName='table1'
set @.Condition='1=1'
set @.Columns=''
set @.ColumnVal=''
select @.Columns =@.Columns + c.Column_name + ',' ,@.ColumnVal=@.ColumnVal +
case c.data_type
when 'varchar' then '''''''+'
when 'datetime' then '''''''+ cast('
else '+ cast(' end +
c.Column_name +
case c.data_type
when 'varchar' then '+ '''''
when 'datetime' then ' as varchar(50)) + '''''
else ' as varchar(50)) +' end + ''','
from INFORMATION_SCHEMA.columns c where c.table_name=@.TableName
set @.Columns= substring(@.Columns,1,len(@.Columns)-1)
set @.ColumnVal= substring(@.ColumnVal,1,len(@.ColumnVal)-1)

exec( 'select ''insert into ' + @.TableName + '('+ @.Columns + ') values (''' + @.ColumnVal + ')'' from ' + @.TableName)

please note that all datatypes are not handled . you will have to modify the case part for other datatypes

|||

You can use one of these excellent options:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

|||Thanks Arnie, clintz, mobin