| Author |
 Topic  |
|
|
ocean
Starting Member
22 Posts |
 Posted - 05/04/2012 : 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
Slovenia
11741 Posts |
Posted - 05/04/2012 : 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 - 05/04/2012 : 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 - 05/04/2012 : 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
Slovenia
11741 Posts |
Posted - 05/04/2012 : 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 - 05/08/2012 : 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
USA
696 Posts |
Posted - 05/08/2012 : 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 - 05/10/2012 : 07:04:07
|
Perfect!
Thanks very much
|
|
|
| |
Topic |
|
COPY WHOLE DATABASE TO ANOTHER
