Quick Update/Insert TSQL?

Hi all!

I have a quick question...

'UPLOAD / INSERT EXISTING CLIENT DATA INTO SQL SERVER FROM CLIENT
INSERT INTO ProductLocal
(tblID,SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date)
SELECT tblID, SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM Product
WHERE (Alias = 'me')

'DOWNLOAD / UPDATE-INSERT EXISTING LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE

What would be the best and least expensive way to UPDATE/INSERT the local client db from the SQL Server?

-- LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE

Any help would be appreciated.... thanks.

Kind regards,

billb

You can use the following approaches,

1. Linked Server,

Set up Linked Serer on your Local Server,

EXEC master.dbo.sp_addlinkedserver

@.server = N'<LinkedServerName>',

@.srvproduct=N'SQLOLEDB.1',

@.provider=N' SQLOLEDB.1',

@.datasrc=N'<YourServer>',

@.provstr=N'Provider=SQLOLEDB.1;Data Source=<YourServer>;Initial Catalog=<Database name>’,

@.catalog=N'<Database Name>'

GO

Exec sp_addlinkedsrvlogin

@.rmtsrvname = N'<LinkedServerName>',

@.useself = false,

@.locallogin = 'sa',

@.rmtuser = 'sa',

@.rmtpassword = '***************'

Now execute the following query..

INSERT INTO ProductLocal

SELECT *

FROM

<LinkedServerName>.<databasename>.<dbo>.Product

2. Ad-Hoc Distributed Quires

Insert Into ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product') AS a;

3. SQL Server Replication

See, http://msdn2.microsoft.com/en-us/library/ms151198.aspx

|||

Was sort of going for just the TSQL Update/Insert though:

Insert Into ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product') AS a;

Something like:

Update ProductLocal

SELECT a.*

FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=<YourServer>; UID=sa; PWD=PASSWORD’, 'Select * from <databasename>.dbo.product where alias = ' & me & ' & ') AS a;

Not sure how to do the above update tsql...

Thanks,

billb