create and poulate database's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create and poulate database's

Author

Topic

gl0bb
Starting Member

5 Posts

Posted - 2005-10-22 : 20:40:31

Hi all,

I need to run a script that will create a number of databases then create tables then populate the tables.
Anyone have any ideas on the struct of such a script
the prepared statement works just fine and i can create 1 db with all of the above OR just 4 db's
ideally i want to ...

CREATE DATABASE a;
CREATE TABLE x();
CREATE DATABASE b;
CREATE TABLE y();
CREATE DATABASE c;
CREATE TABLE z();
CREATE TABLE xan();
CREATE DATABASE d;

The above is to be done on a sql 2005 server with jdbc driver
I have no probs connecting to the db and executing commands ie select delete update if the table exists.
If i just create the bd's it executes fine or if i create 1 db it ececutes fine.

Any help will be mostly appreciated.

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 00:38:30

Do you need to switch context to the database you've just created before creating the tables?


CREATE DATABASE a;
USE a;
CREATE TABLE x();

Kristen

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-23 : 03:06:15

Hi,
may use it like this tooo ...

create database test
go
create table test.dbo.testtbl
(
testcol char(1)
)
go

http://www.activecrypt.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 01:30:21

I prefer the use of DBName.ownerName.ObjectName than USE DB as USE DB is not permitted in Stored Procedure

Madhivanan

Failing to plan is Planning to fail

gl0bb
Starting Member

5 Posts

Posted - 2005-10-24 : 06:46:57

Thanks to all who responded,
i found that it was actually a java error as i was trying to do to much with one prepared statement.

i have parsed the script and broken it into an array and submitted the script as such
a[0] create db ...
a[1] use...
a[2] create table ...

and woot it works great but...

there is always a but

i am using another bit of code to query the catalogue.
this returns

DatabaseMetaData Information

============================

- Product Name : Microsoft SQL Server

- Product Version Number : 9.0.1116

- Database Major Version : 9

- Database Minor Version : 0

- Driver Name : Microsoft SQL Server 2005 JDBC Driver - Beta 2

- Driver Major Version : 1

- Driver Minor Version : 0

- Username : sa

- Catalogs -

* AdventureWorks

* AdventureWorksDW

* Edinburgh

* Glasgow

* jGuru

* master

* model

* msdb

* Paisley

* ReportServer

* ReportServerTempDB

* Stirling

* tempdb

- Tables Types -

* SYSTEM TABLE

* TABLE

* VIEW

- Tables -

* master * dbo * spt_fallback_db * TABLE * null

* master * dbo * spt_fallback_dev * TABLE * null

* master * dbo * spt_fallback_usg * TABLE * null

* master * dbo * spt_monitor * TABLE * null

* master * dbo * spt_values * TABLE * null

* master * dbo * MSreplication_options * TABLE * null

* master * dbo * Branch * TABLE * null

* master * dbo * Product * TABLE * null

* master * dbo * Staff * TABLE * null

db successfully closed...

note
* master * dbo * Branch * TABLE * null

* master * dbo * Product * TABLE * null

* master * dbo * Staff * TABLE * null

why do these tables appear in the master catalogue?

I am doing this as a project where i need to simulate a distributed db
so need to create catalogues for
Glasgow, Edinburgh, Stirling and Paisley
my application should detect what location above i am at (programming issue i have this sorted i hope)
query the local catalogue to find out where the data in my query is then query those catalogues for confirmation and then execute the query

Regards Graham

gl0bb
Starting Member

5 Posts

Posted - 2005-10-24 : 06:58:01

Sorry i should have stated i take it i need to create a system table for each database

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources