How do I create an SQL statement to....

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

SQL Server 2005 Forums

Transact-SQL (2005)

How do I create an SQL statement to....

Author

Topic

JDrown
Starting Member

10 Posts

Posted - 2007-10-23 : 08:58:55

select all rows from one table in one database and insert them all into another table(with the same name and same structure) in another database on the same server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 09:00:42

Use qualified names

INSERT TargetDatabase.Owner.Table
SELECT Col1, Col2
FROM SourceDatabase.Owner.Table

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 09:03:09

quote:
Originally posted by Peso

Use qualified names

INSERT TargetDatabase.Owner.Table(Col1, Col2)
SELECT Col1, Col2
FROM SourceDatabase.Owner.Table

E 12°55'05.25"
N 56°04'39.16"

For safer side

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 09:10:37

Or if the table does not exist in the target database you can sue the shorthand:

SELECT Col1, Col2
INTO TargetDatabase.Owner.Table
FROM SourceDatabase.Owner.Table

which will create the table, with all columns of appropriate datatype, but will NOT create indexes, foreign keys, etc.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 09:13:51

quote:
Originally posted by Kristen

Or if the table does not exist in the target database you can sue the shorthand:

SELECT Col1, Col2
INTO TargetDatabase.Owner.Table
FROM SourceDatabase.Owner.Table

which will create the table, with all columns of appropriate datatype, but will NOT create indexes, foreign keys, etc.

Kristen

Did I see any relation between red parts?

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-10-23 : 09:32:13

quote:
Originally posted by Kristen

Or if the table does not exist in the target database you can sue the shorthand:

SELECT Col1, Col2
INTO TargetDatabase.Owner.Table
FROM SourceDatabase.Owner.Table

which will create the table, with all columns of appropriate datatype, but will NOT create indexes, foreign keys, etc.

Kristen

Kristen, It works. But what, if i have to Update?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 09:37:17

quote:
Originally posted by mahesh_bote

quote:
Originally posted by Kristen

Or if the table does not exist in the target database you can sue the shorthand:

SELECT Col1, Col2
INTO TargetDatabase.Owner.Table
FROM SourceDatabase.Owner.Table

which will create the table, with all columns of appropriate datatype, but will NOT create indexes, foreign keys, etc.

Kristen

Kristen, It works. But what, if i have to Update?

Update T
set col1=S.col1, col2=S.col2
from db1.dbo.source S inner join db2.dbo.target T
on S.keycol=T.keycol

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 09:43:18

"But what, if i have to Update?"

Well then the table will exist, so this "cheats" method of creating a table won't be needed, will it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources