Insert between separate databases

[John]

Hi

I have an insert sql that inserts selected records from one table into
another. Is there a way to specify in the insert statement that one table is
from one database and the second table is from another database i.e. insert
is between tables of two separate databases?

Thanks

Regards

 

[Frans Bouma [C# MVP]]

Hi

I have an insert sql that inserts selected records from one table into
another. Is there a way to specify in the insert statement that one table is
from one database and the second table is from another database i.e. insert
is between tables of two separate databases?

depends on the database system used. If you use sqlserver, and you have
both catalogs in the same server, you can do:
INSERT INTO [cataloga].[schemaowner].

(field1, field2,...)
SELECT field1, field2, ...
FROM [catalogb].[schemaowner].

where...


schemaowner is usuablly 'dbo'

If they're on separate boxes, you can link one server into another
server. You then get yet another name in front of the table name:
...
FROM [server].[catalogb].[schemaowner].

...

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[John]

One is a local access database (i.e. on the same box as the .net code being
run on), the other is a remote sql server which I can access via ip address.
Am I doomed?

Thanks

Regards

Hi

I have an insert sql that inserts selected records from one table into
another. Is there a way to specify in the insert statement that one table
is from one database and the second table is from another database i.e.
insert is between tables of two separate databases?

depends on the database system used. If you use sqlserver, and you have
both catalogs in the same server, you can do:
INSERT INTO [cataloga].[schemaowner].

(field1, field2,...)
SELECT field1, field2, ...
FROM [catalogb].[schemaowner].

where...


schemaowner is usuablly 'dbo'

If they're on separate boxes, you can link one server into another server.
You then get yet another name in front of the table name:
..
FROM [server].[catalogb].[schemaowner].

...

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

One is a local access database (i.e. on the same box as the .net code being
run on), the other is a remote sql server which I can access via ip address.
Am I doomed?

No .
You can link the remote sqlserver database inside access if I'm not
mistaken, as a set of tables.

Which data is merged into which one btw? Access into sqlserver?

FB

Thanks

Regards

Hi

I have an insert sql that inserts selected records from one table into
another. Is there a way to specify in the insert statement that one table
is from one database and the second table is from another database i.e.
insert is between tables of two separate databases?

depends on the database system used. If you use sqlserver, and you have
both catalogs in the same server, you can do:
INSERT INTO [cataloga].[schemaowner].

(field1, field2,...)
SELECT field1, field2, ...
FROM [catalogb].[schemaowner].

where...


schemaowner is usuablly 'dbo'

If they're on separate boxes, you can link one server into another server.
You then get yet another name in front of the table name:
..
FROM [server].[catalogb].[schemaowner].

...

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[John]

Both ways unfortunately. Thanks

Regards

One is a local access database (i.e. on the same box as the .net code
being run on), the other is a remote sql server which I can access via ip
address. Am I doomed?

No .
You can link the remote sqlserver database inside access if I'm not
mistaken, as a set of tables.

Which data is merged into which one btw? Access into sqlserver?

FB

Thanks

Regards

John wrote:
Hi

I have an insert sql that inserts selected records from one table into
another. Is there a way to specify in the insert statement that one
table is from one database and the second table is from another
database i.e. insert is between tables of two separate databases?
depends on the database system used. If you use sqlserver, and you have
both catalogs in the same server, you can do:
INSERT INTO [cataloga].[schemaowner].

(field1, field2,...)
SELECT field1, field2, ...
FROM [catalogb].[schemaowner].

where...


schemaowner is usuablly 'dbo'

If they're on separate boxes, you can link one server into another
server. You then get yet another name in front of the table name:
..
FROM [server].[catalogb].[schemaowner].

...

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Paul Clement]

¤ Both ways unfortunately. Thanks
¤

Yes, you can insert rows from a table in one database to a table in a different database.

So are you inserting rows from one table that are not present in the other? Do each of the tables
have primary keys or is there some other criteria by which the insert should occur?


Paul
~~~~
Microsoft MVP (Visual Basic)