Create table in other database

Question

Create table in other database

tmccar

SSC Eights!

Points: 990
More actions
September 5, 2011 at 7:54 am

#242831

When I create a table in SQL Server Management Studio, it always gets created in the master database. How can I get it to create the table in a different database?
I created the table using an sql script like so:
Create table oe25 (
id int identity(1,1) not null,
Project__char(7),
Newl_order_notinyint,
Work_order_numbervarchar(4),
Customers_ref_varchar(60)
go

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

tmccar (9/5/2011)
When I create a table in SQL Server Management Studio, it always gets created in the master database. How can I get it to create the table in a different database?
I created the table using an sql script like so:
Create table oe25 (
id int identity(1,1) not null,
Project__char(7),
Newl_order_notinyint,
Work_order_numbervarchar(4),
Customers_ref_varchar(60)
go

USE YourDatabaseName

GO

Create table oe25 (

id int identity(1,1) not null,

Project__char(7),

Newl_order_notinyint,

Work_order_numbervarchar(4),

Customers_ref_varchar(60)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmccar SSC Eights! Points: 990 More actions · Answer 2

tmccar

SSC Eights!

Points: 990

September 5, 2011 at 8:12 am

#1377052

Thanks Corgi for quick reply

Yes, that does it!

Tom

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I'm glad that it worked out for you.

You can also select the Database in SSMS.

There is a drop down that specifies the current database in the top left hand corner.

Regards.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tmccar SSC Eights! Points: 990 More actions · Answer 4

Would you know, if there is an easy way to create a lot of other tables like this one? I have all the schema details and I've been looking for a way to create them all in one go.

kazim.raza SSCrazy Points: 2390 More actions · Answer 5

Paste them all in one query window and hit execute, it would create them all in one go. You would want to arrange the sequence of the scripts though to manage foreign keys and relationship / referential integrity.

Regards

Kazim Raza

tmccar SSC Eights! Points: 990 More actions · Answer 6

tmccar

SSC Eights!

Points: 990

September 5, 2011 at 9:22 am

#1377074

OK, I will give it a go, thanks

tmccar SSC Eights! Points: 990 More actions · Answer 7

Got all 167 tables created (after copying and pasting the schemas from Excel) and after a lot of debugging. I now have a text file of records for each table. What's the easiest way to populate all tables?

baabhu SSCertifiable Points: 6205 More actions · Answer 8

The other approach will be change the default database name from Master to the database name that you use for the login.

EXEC sp_defaultdb 'Victoria', 'AdventureWorks2008R2'

This makes when ever the particular login connects sqlserver will be connected the particular database.

webtekkie SSCommitted Points: 1901 More actions · Answer 9

One observation; you aren't creating a primary key on this table (and presumably any of the other one's you are creating). When you create a primary key, SQL automatically creates a clustered index on the key column which is great for query performance.

Change your query to something like this to resolve this...

Create table oe25 (

id int identity(1,1) not null constraint pk_oe25_id primary key,

Project__ char (7) ,

Newl_order_no tinyint ,

Work_order_number varchar (4) ,

Customers_ref_ varchar (60))

kazim.raza SSCrazy Points: 2390 More actions · Answer 10

You should have clarified the data source, it would not have taken you a lot of debugging. You can get the data into your tables through import/export data option. You can get there by right clicking on the database >> tasks >> import/export data.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

tmccar (9/5/2011)
Would you know, if there is an easy way to create a lot of other tables like this one? I have all the schema details and I've been looking for a way to create them all in one go.

Once you specify the Database by using the USE Statement, all of the Databases will be created in the same Database.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/