SQL script syntax for multiple databases under one user

Question

This is probably stupid simple, but for some reason I'm having trouble getting it to work. I have a typical import script I'm trying to run on a MS SQL server with one master user (as opposed to a single user with only access to one database).

When I run the .SQL script, it creates the database and then starts to create tables. Here's where it gets interesting. It's not creating the databases under the DB I just made. It's throwing the tables under the "System Databases" view and not restricting the table creation to the DB that was just created.

I have tried:

CREATE TABLE table_name
CREATE TABLE database_name.table_name

Maybe I'm overlooking something really easy. I don't usually run into this with MySQL with a single user mapped to one database, I think since the user can only see that one database, so MySQL assumes it must be the one to work with.

The difference now is that I'm using MSSQL 2008 and maybe it works a little differently and I'm overlooking something. Thanks for your help!

Tried this too. No luck. Says database doesn't exist when it tries to create the table. I would think being a top/down read of the query script it would first create the database, then try to create the table afterwards.

CREATE DATABASE DATABASENAME;
CREATE TABLE DATABASENAME.dbo.TABLENAME
  (
     field_one   VARCHAR(100) NOT NULL,
     field_two INT NOT NULL,
     PRIMARY KEY(field_one)
  )

This is a working example after getting it all figured out. This syntax works well and I don't need to specify the DBO pathing stuff before table names this way. Cleaner and got me the results I was looking for. Thanks everyone.

IF Db_id('DBNAME') IS NULL
  CREATE DATABASE DBNAME;

GO

USE [DBNAME];

GO

CREATE TABLE TABLENAME
  (
     COL1   VARCHAR(100) NOT NULL,
     COL2 INT NOT NULL,
     PRIMARY KEY(COL2)
  )

INSERT INTO TABLENAME
            (COL1,
             COL2)
     VALUES('1234',1001),
            ('1234',1002),
            ('1234',1003),
            ('1234',1004)

It basically just does a check to make sure database is created before doing anything else, then sets the USE database to the one I'm working with. Everything else is just normal SQL, so have fun. Cheers!

pcofre · Accepted Answer · 2011-03-29 02:54:28Z

1

Probably you need to include the USE sentence at the begining of your script in order to indicate the database as follows:

USE [database_name]
GO

By default SQL-SERVER use the master DB that´s listed under system databases.

Other way is to use the database prefix, but including the owner:

INSERT INTO database_name.dbo.table_name
INSERT INTO database_name..table_name

answered Mar 29, 2011 at 2:54

pcofre

4,07420 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Will Ashworth Over a year ago

It's telling me that the database doesn't exist now trying your second method calling the path/owner with DBO. Weird. Posting updated trial above in OP.

Will Ashworth Over a year ago

I think using USE requires that the database exist already. The creation of the database is included within the .SQL file(s). Trying to get the script to create everything and import everything in one run if possible.

Will Ashworth Over a year ago

Bah. Getting frustrated. Just found this. Gonna test it out: stackoverflow.com/questions/3373268/…

Will Ashworth Over a year ago

OK, That did it. It was more or less what you told me, I just wasn't doing it 100% right. I'll post the correct finished syntax in my upper example.

Collectives™ on Stack Overflow

SQL script syntax for multiple databases under one user

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related