Re: Insert between separate databases

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]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in message news:OoY5Et$tIHA.3484@xxxxxxxxxxxxxxxxxxxxxxx
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#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: Fatal error 682 and SqlDependency
    ... Cannot execute as the database principal because the ... However, this error is unrelated to the size of the query notification, is ... When this error happens in the server messages for one query notification ... SqlServer 2005 Notification Services feature to get notified about changes ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MySQL used with VB .NET 1.0
    ... Whatever process you eventually develop for getting the data from an EXCEL spreadsheet on your machine into a database on your ISPs server will be much more complicated than a simple CSV export and FTP. ... I don't think that much people here use MySql as SqlServer Express from Microsoft is freeware. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sharepoint migration "IIS application pool username is invalid
    ... database from the remote SQL server. ... it displays the webserver name of the source server. ... to change the default content database server to the remote SQL server via ... >>> Is your IIS server included as part of the domain? ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Data Access Layer that allows Multiple Data Sources
    ... I'm looking for something that allows a typical master data source for storing and retrieving data, but I'd also like it to be able send select data to other datasources. ... database server and it would be nice to be able to run these reports ... write it back in sqlserver, pull data from sqlserver A in context 1 and ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Upgrading Access 97 application to dotNet
    ... for the database back-end. ... database must reside on the network server, ... like SQLServer. ...
    (microsoft.public.dotnet.languages.vb)