Copy one database table to another database

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 one database table to another database

Author

Topic

sunrag77
Starting Member

8 Posts

Posted - 2009-07-30 : 02:48:37

Hi

Is it possible to copy one database table to another database. If yes, how, kindly help.

Regards
Sundaram

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 02:53:46

ya possible

Copy only the schema
select * into db2.dest_table from db1.source_table where 1=2

Copy with data
select * into db2.dest_table from db1.source_table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 02:59:20

quote:
Originally posted by senthil_nagore

ya possible

Copy only the schema
select * into db2.dest_table from db1.source_table where 1=2

Copy with data
select * into db2.dest_table from db1.source_table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hi

I think it should be like this...

USE DB2
SELECT * INTO <TABLE_NAME>FROM DB1..<TABLE_NAME>

SELECT * FROM Database.schema.TableName

SELECT * FROM MASTER.DBO.SYSOBJECTS
-------------------------
R..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 03:03:18

hi raj,

for copying one database table to another database table
u can use like this or u specified mention also

select * into db2.schemaname.tablename from db1.schemaname.tablename

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 03:03:32

quote:
Originally posted by rajdaksha

quote:
Originally posted by senthil_nagore

ya possible

Copy only the schema
select * into db2.dest_table from db1.source_table where 1=2

Copy with data
select * into db2.dest_table from db1.source_table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hi

I think it should be like this...

USE DB2
SELECT * INTO <TABLE_NAME>FROM DB1..<TABLE_NAME>

SELECT * FROM Database.schema.TableName

SELECT * FROM MASTER.DBO.SYSOBJECTS
-------------------------
R..

Both are Same!

In your case you change the Database and execute the query.But i execute execute from the source DB

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 03:07:49

quote:
Originally posted by senthil_nagore

quote:
Originally posted by rajdaksha

quote:
Originally posted by senthil_nagore

ya possible

Copy only the schema
select * into db2.dest_table from db1.source_table where 1=2

Copy with data
select * into db2.dest_table from db1.source_table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hi

I think it should be like this...

USE DB2
SELECT * INTO <TABLE_NAME>FROM DB1..<TABLE_NAME>

SELECT * FROM Database.schema.TableName

SELECT * FROM MASTER.DBO.SYSOBJECTS
-------------------------
R..

Both are Same!

In your case you change the Database and execute the query.But i execute execute from the source DB

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

its should be this...
SELECT * INTO TEMPDB..#TEMP FROM Table_name

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 03:09:57

Hi

Copy with data
select * into db2.dest_table from db1.source_table

I think this will take schema not database.....

-------------------------
R..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 03:19:28

hi raj
we can use this
select * into db2.schemaname.tablename from db1.schemaname.tablename
it will work
or
USE DB2
SELECT * INTO <TABLE_NAME>FROM DB1..<TABLE_NAME>

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 03:21:47

Hi raj,

if use specify the TEMPDB like u specifed

SELECT * INTO TEMPDB..#TEMP FROM Table_name

It says like this
Database name 'TEMPDB' ignored, referencing object in tempdb.

Because "#" represent the Temp table so it automatically refer the Temp DataBase.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 03:47:21

quote:
Originally posted by bklr

hi raj
we can use this
select * into db2.schemaname.tablename from db1.schemaname.tablename
it will work
or
USE DB2
SELECT * INTO <TABLE_NAME>FROM DB1..<TABLE_NAME>

hi

Both will work..we can use any one...right.. correct me if am in wrong..

-------------------------
R..

sunrag77
Starting Member

8 Posts

Posted - 2009-07-30 : 03:48:58

Hi all
Thks for the reply, but my one database is in one server and another in my local.
How will i copy this. Pls help.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 03:50:35

quote:
Originally posted by senthil_nagore

Hi raj,

if use specify the TEMPDB like u specifed

SELECT * INTO TEMPDB..#TEMP FROM Table_name

It says like this
Database name 'TEMPDB' ignored, referencing object in tempdb.

Because "#" represent the Temp table so it automatically refer the Temp DataBase.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Hi

ya i know ..here one of my local database name is tempdb1 instead of that i was put tempdb sorry....

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 03:53:44

quote:
Originally posted by sunrag77

Hi all
Thks for the reply, but my one database is in one server and another in my local.
How will i copy this. Pls help.

Hi

1. use linked server
select * into db2.schemaname.tablename from servername.db1.schemaname.tablename
2.use Import and Export wizard
3.use SSIS
4.use openquery

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources