Local MSDE export->Online SQL - Xtreme Visual Basic Talk

02-22-2006, 01:58 PM

abev107

Junior Contributor

Join Date: Oct 2003

Location: New York

Posts: 225

Local MSDE export->Online SQL

I have a local MSDE (SQL Server 2005) on my local comp and I am developing it for a web server.

I am mostly done with the development, but now I have found that some tweaks are necessary on the local db and I would like to find an easier way to sync the data with the web server db?

Is there such as thing as:

Select from local db
Insert into web server db

I dont know if theres any option with SQL Server 2005 like there is with SQL 2000 DTS?

__________________
Vor lauter Bäumen den Wald nicht sehen.

02-22-2006, 10:47 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,386

Ref this link. The approach to link the two sql servers would be the same.

Assuming that the tables are already present on the target database, the query to insert from one table to another would be something similar to

Code:

INSERT INTO LocalTable  
SELECT * 
FROM fully.qualified.remotetable

A similar approach can be followed to CREATE a new table & populate it in one SQL statement. Ref your INSERT and CREATE TABLE references in SQL Server Books Online.

__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 02-22-2006 at 10:53 PM.

02-23-2006, 08:37 AM

abev107

Junior Contributor

Join Date: Oct 2003

Location: New York

Posts: 225

Thanks Lou,

was easier that I thought. Problem is I need to:

Code:

Insert into RemoteTable
Select * from LocalTable

Is there a way to use "Insert" to a remote table?

I can do this:

Code:

Select * from OpenQuery(PRLL, 'Select * from tblLogin')

But I cannot do this:

Code:

Insert OpenQuery(PRLL, 'Select * from tblLogin')

The syntax is wrong on the Insert - Is there a way to refer to the remote table with an Insert?

__________________
Vor lauter Bäumen den Wald nicht sehen.

02-23-2006, 01:40 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,386

Never tried the reverse.

Have you tried this?

Code:

Insert into fully.qualified.RemoteTable 
Select * from LocalTable