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
