COPY WHOLE DATABASE TO ANOTHER

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

COPY WHOLE DATABASE TO ANOTHER

Author

Topic

ocean
Starting Member

22 Posts

Posted - 2012-05-04 : 11:27:47

Hi Guys

I tried many different queries, and they are not working. I just need to copy an entire table from a database called A to a database called B.

Any ideas?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2012-05-04 : 11:35:56

select * into dbB.schemaName.TableName from dbA.schemaName.TableName

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 2.0 out!

SQL Server MVP

ocean
Starting Member

22 Posts

Posted - 2012-05-04 : 11:40:14

[quote]Originally posted by spirit1

select * into dbB.schemaName.TableName from dbA.schemaName.TableName

___________________________________________________________________________

What if, I dont have a table in the new database. The table that I need Is exactly the one that im copying it

Thank you

ocean
Starting Member

22 Posts

Posted - 2012-05-04 : 11:46:31

[quote]Originally posted by spirit1

select * into dbB.schemaName.TableName from dbA.schemaName.TableName

___________________________________________________________________________

msg 2760, level 16, state 1, line 1
The specified schema name XXX either does not exist or you do not have permission to use it.

I created XXX database...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2012-05-04 : 11:55:45

if you don't have a schema then your schema is dbo.

the command i gave you will create a new table in the database.
if you already have a table then you need to use:
Insert into DB1.dbo.yourNewTable(columns)
select columns from DBA.dbo.AyourTableInotherDatabase

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 2.0 out!

SQL Server MVP

ocean
Starting Member

22 Posts

Posted - 2012-05-08 : 05:28:17

[quote]Originally posted by spirit1

if you don't have a schema then your schema is dbo.

the command i gave you will create a new table in the database.
if you already have a table then you need to use:
Insert into DB1.dbo.yourNewTable(columns)
select columns from DBA.dbo.AyourTableInotherDatabase

___________________________________________________________________________

Hi there

I was using the schema as the database and the error was msg 2760, level 16, state 1, line 2

Is not working,

insert into Database_Final.Table_exchange ((column 1), (column 2), (column 3), (column 4)) select columns from
Database_Where_I_Have_The_Info.Table_With_The_Info_I_Need

msg 120, level 15, state 1, line 1 'The select list for the insert statement contains fewer items than the insert list'

The table_exchange has the same number of columns and the same varchar 4 etc as the table that I want to export the info from...

I tried in different ways, but still not working

use Database_Final;
create table_exchange like Database_Where_I_Have_The_Info.Table_With_The_Info_I_Need;
insert into table_exchange
select * from Database_Where_I_Have_The_Info.Table_With_The_Info_I_Need

msg 156, level 15, state 1, line 2

I just need to copy a table from an existing database to another database (creating or using an existing table, i dont mind)

Thanks

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-05-08 : 13:42:26

You need to use 3-part naming, which is: {database}.{schema}.{object}

You example would be:

INSERT INTO [Database_Final].dbo.Table_exchange(column1, column2, column3, column4)
SELECT column1, column2, column3, column 4 FROM [Source_Database].dbo.[Source_Table]

If the table does not already exist - use the following:

SELECT column1, column2, column3, column4
INTO [Database_Final].dbo.Table_Exchange
FROM [Source_Database].dbo.[Source_Table]

Replace [Source_Database] with the actual database name where the data is coming from, [Source_Table] with the actual source table, [Database_Final] with the actual destination database.

ocean
Starting Member

22 Posts

Posted - 2012-05-10 : 07:04:07

Perfect!

Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources