Thankyou Tore. I have a very small and basic SQL Server 7 database containing 3 tables. I would like to distribute the tables and the data to my customers in the form of some scripts which they can run on their own SQL Server(s) and have the tables created and the data inserted.
Ive created an SQL script to create the tables, I was just wondering if theres a way I can create an SQL script to create the data into those tables? (I should have phrased my question like that to start with, I should know better by now!) Cheers Sam Thompson ---------------------------------------------- 2cs Communications Ltd http://www.2cs.com [EMAIL PROTECTED] T: 01473 622263 F: 01473 622515 ----------------------------------------------- ----- Original Message ----- From: "Bostrup, Tore" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Tuesday, September 10, 2002 3:45 PM Subject: RE: generate SQL scripts and include data? > 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.unsub%% > --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
