I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

I want to move an existing table from another

I want to move an existing table from another.

Example

B.mdf dbo.xx toC.mdf dbo.xx

B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx

C.mdf dbo.xx is empty -

All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx

I was telling I could do a cut and paste to copy the dbo.xx

Thank you in advance.

~ Peaches ~

With SQL 2000 use DTS to copy to contents. With SQL2005 use SSIS.

|||

There is another way and you don't need to create the target table first:

You can copy database tables from one database to another. Here is a sample for you to move one table from one database to another database without creating the new table first.

Run this from the database you want to export data:

SELECT*INTO newDataBaseName.dbo.yourNewTableFROM tableToExpost

|||

Couple of things to keep in mind are:

(1) If you already created the target table with PK-FK relationships, indexes, you can either use DTS or

INSERT INTO TargetDB.dbo.TargetTable (<columns>)

SELECT <columns> FROM SourceDb.dbo.SourceTable

(2) If the target table does not exist, then you can uss limno's approach and do a SELECT * INTO TargetDb.dbo.TargetTable FROM SourceDB.dbo.SourceTable WHERE <Condition>

(3) IF there is lot of data ( as in > few million rows) you might also want to look into BCP utility in Books On Line.

|||

Thank you very much !!!!!

I was able to take care of this in 5 minutes ...

Whew::: This makes my life easier today.

~ Peaches ~

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:

Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams

I need help using DTS Import/Export tool.

I am trying to use MS SQL Server 2000's Export/Import DTS tool to transfer an existing database on my pc to a remote web server. I am able to connect to the remote database that my web hosting package provided (and thanks to all of you who helped with with the connection issue). Now when I selected the option "Copy Tables and Views From Data Source" to do my transfer, the process completed successfully, but when I tried to do some queries, I get incorrect results. It seems like only the "table values" in my source database was copied. The data that describes how these tables are related (ie. constraints, primary keys, foreign keys, etc) seemed to have NOT been copied to the web database.

So I went back to do the transfer again, this time choosing the option "Copy Objects and Data Between SQL Servers". Now the transfer process proceeds smoothly for the first half (the first half seems to be the same as described above -- just copying the table values). But as it started to copy object data (around 59% done or so), it errored out with the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Only members of the sysadmin role or the database owner may set database options"

Then I went back to the options tab, and tried deselecting some of them. I tested a lot of various combinations, and finally I unchecked them ALL. But still I get the same error. Now, I dont know what "database options" it's referring to.

However, the odd thing is that even though the transfer process failed halfway through, my queries seem to be getting correct results now. I havent tested it much but the few that I tried out returned expected results.

My concern now is that why doesnt this tranfer process complete without errors? I'm worried that since there are still problems with the data transfer process, then there must still be (possibly hidden) problems with the "incomplete" copy of the database on the web server.

Can anyone pinpoint the cause of this?in my experience, when transferring multiple tables with DRI from one server to another, there are sometimes problems related to precedence. For example, when you tranfer relationships, keys, etc... the job might fail. What happens is that the process attempts to create the table with FK reference before the primary table. I havent seen the precise error that you are experiencing... but, would make sure that this is not happening to you...

Sometimes permissions are applied before the user has been created, resulting in an error...

you can also script the relationshipes, keys, etc... and apply them manually after all data has been copied.