Posted by Erland Sommarskog on 02/13/07 22:37

farseer (farseer@optonline.net) writes:
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?

SELECT 'bcp yourdb..' + name + ' out ' + name + '.bcp -n -T'
FROM sys.objects
WHERE type = 'U'

Copy result into a BAT file and run.

The to load:

SELECT 'bcp yourtargetdb..' + name + ' in ' + name + '.bcp -n -T ' +
'-S REMOTSER' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE o.object_id = c.object_id
AND c.is_identity = 1)
THEN ' -E '
ELSE ''
END
FROM sys.objects o
WHERE type = 'U'

I assume that you are not using any data types that are not available
in SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]