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

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].[table] (field1, field2,...)
SELECT field1, field2, ...
FROM [catalogb].[schemaowner].[table]
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].[table]...

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#)
------------------------------------------------------------------------

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

"Frans Bouma [C# MVP]" <(E-Mail Removed)> wrote in message
news:OoY5Et$(E-Mail Removed)...
> 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].[table] (field1, field2,...)
> SELECT field1, field2, ...
> FROM [catalogb].[schemaowner].[table]
> 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].[table]...
>
> 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 wrote:
> 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
>
> "Frans Bouma [C# MVP]" <(E-Mail Removed)> wrote in message
> news:OoY5Et$(E-Mail Removed)...
>> 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].[table] (field1, field2,...)
>> SELECT field1, field2, ...
>> FROM [catalogb].[schemaowner].[table]
>> 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].[table]...


--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------

Both ways unfortunately. Thanks

Regards

"Frans Bouma [C# MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> John wrote:
>> 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
>>
>> "Frans Bouma [C# MVP]" <(E-Mail Removed)> wrote in message
>> news:OoY5Et$(E-Mail Removed)...
>>> 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].[table] (field1, field2,...)
>>> SELECT field1, field2, ...
>>> FROM [catalogb].[schemaowner].[table]
>>> 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].[table]...
>
>
> --
> ------------------------------------------------------------------------
> 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#)
> ------------------------------------------------------------------------

On Sun, 18 May 2008 14:22:15 +0100, "John" <(E-Mail Removed)> wrote:

¤ 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)