What is it you are trying to accomplish?
I doubt that you'd *really* want a SQL script to move the contents of a
database, except for a very small one.
There are plenty of tools and approaches available for moving data/databases
- take your pick. Most tools (including the below mentioned tools) target
one table at a time.
* For (repeated) synchronization of multiple databases, look into
replication.
* For a complete database move, a couple of options have been suggested in
previous posts (use backup/restore or detach/attach database). Other
options exist - check BOL.
* For speed of data transfer, use BCP for export and import. You can
alternatively use BULK INSERT for import of a BCP export file. You can use
BCP export with a query if you want a selection only. Note that when using
BCP/BULK INSERT, the "paths" should be as they are seen from the SQL Server,
and use forward slashes instead of backward slashes.
* For combination of speed and customization, use DTS. You can script the
transfer any which way you want, and EM will create your basic transfer
script.
* If you have a small database, you *could* technically use a select
statement such as this:
SELECT 'INSERT INTO <Table> (<ColumnList>) VALUES ('
+ Sep1 + <Column1> + Sep1 + ', '
+ Sep2 + <Column2> + Sep2 + ', '
+ <etc.>
FROM TABLE
but the import performance will be abysmal for any sizable table.
* You can search the web for database tools.
* You can create a stored procedure or an app to enumerate the tables and
create scripts for each table. Or you can use a select from
INFORMATION_SCHEMA to create (small) per-table scripts:
SELECT 'BCP ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out <PathFromServer>/' + TABLE_CATALOG + '/' + TABLE_NAME
+ '.dat -S<SQLServer> [-U<User> -P<Pwd>] -m1 -n' As BCPCmd
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<MyDB>'
AND TABLE_TYPE = 'BASE TABLE'
HTH,
Tore.
-----Original Message-----
From: Sam Thompson [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 10, 2002 10:11 AM
To: ActiveServerPages
Subject: generate SQL scripts and include data?
Like the subject line says, it is possible to use SQL Server 7 or 2000 to
generate SQL scripts which *include* the data and the structure, instead of
just the structure?
Cheers dudes.
Sam Thompson
----------------------------------------------
2cs Communications Ltd
http://www.2cs.com
[EMAIL PROTECTED]
T: 01473 622263
F: 01473 622515
-----------------------------------------------
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
