Personally, this is what I would do: I would separate the database creation script from the data, and provide a two-step process:
1: Create the Database using the database script. If the user wants to customize users, rights, etc., this can either be done by editing the script prior to running it, or modifying the database after creation but before populating it. 2: Provide a small app that uses BULK INSERT, combined with a check that the tables are indeed empty. You could provide a batch file with BCP scripts instead. (Use batch parameters for file paths, Server name and User/Pwd if required). If you wanted to make it a single step, encapsulate both the above steps in your "setup" app. You could even provide configurability for user names, database names, etc. if you wanted to make it self-contained. Note: In order to create the database in the first place, you'd have to log in as a DBA, preferably to the MASTER database on the server. To create the basis for these two steps, use EM to generate the database scripts, and use BCP to generate the data files. You can create the BCP commands with a SQL statement similar to the one I provided in my previous post. BULK INSERT is a SQL Statement and is very similar in use to BCP. Being a SQL statement, you can easily put it in an app. Or you could probably use DTS to export to an .mdb or similar, and provide a script to import from the .mdb. I haven't used DTS much (BCP has provided me the functionality I have wanted), so I'm not sure how well suited it would be for an initial distribution of a new database. HTH, Tore. -----Original Message----- From: Sam Thompson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 11:20 AM To: ActiveServerPages Subject: Re: generate SQL scripts and include data? 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.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
