Here is an MS SQL statement that creates an SQL script containing INSERT statements for all of the data in all of the tables in the current schema:
select
case when c.ordinal_position = 1
then 'SELECT ''INSERT INTO "' + t.table_name + '" VALUES ('''
else ''
end,
' + coalesce('
+ case when patindex('%char%', c.data_type) <> 0
or patindex('%text%', c.data_type) <> 0
or patindex('%date%', c.data_type) <> 0
or patindex('%time%', c.data_type) <> 0
then ''''''''' + replace(' + c.column_name + ', '''''''', '''''''''''') + '''''''''
else 'convert(varchar, ' + c.column_name + ')' end
+ ', ''NULL'')',
case when c.ordinal_position <> all(select max(ordinal_position)
from information_schema.columns
where table_name = t.table_name )
then ' + '','''
else ' + '');'' FROM "' + t.table_name
+ case when t.table_name = all(select max(table_name)
from information_schema.tables
where table_type = 'BASE TABLE'
and table_name <> 'dtproperties'
)
then '";' else '" UNION' end
end
from information_schema.tables as t
join information_schema.columns as c on (t.table_name = c.table_name)
where t.table_type = 'BASE TABLE'
and t.table_name <> 'dtproperties'
order by t.table_name, c.ordinal_position
It doesn't handle all the data types, but works well for small databases. The statement could be adapted to other dialects of SQL.
