insert and update rows within same SP

[richey1]

i'd like to have a stored procedure which either performs a bulk insert, bulk update or does nothing from a table on one server (CTS_CUSTOMERS - ServerA) to another on a different server (CTS_CUSTOMERS - ServerB)

firstly if the record does not exist on serverB then insert it from serverA, something like below ?

INSERT INTO serverB.databaseB.CTS_CUSTOMERS (CustID) SELECT CustID FROM serverA.databaseA.CTS_CUSTOMERS AS S WHERE NOT EXISTS (SELECT CustID FROM serverB.databaseB.CTS_CUSTOMERS WHERE serverB.databaseB.CustID = S.CustID)

if it does already exist, then compare the last time the record was updated between the two database tables (column name is called last_update) and if it is different then run an update.

update serverB.databaseB.CTS_CUSTOMERS
set serverB.databaseB.CTS_CUSTOMERS.CustID = serverA.databaseA.CTS_CUSTOMERS.CustID
serverB.databaseB.CTS_CUSTOMERS.last_update = serverA.databaseA.CTS_CUSTOMERS.last_update

from
serverB.databaseB.CTS_CUSTOMERS d1
inner join serverA.databaseA.CTS_CUSTOMERS d2
on d1.CustID= d2.CustID
where d1.last_update <> d2.last_update

else do nothing !
if i run this every day I suppose i only need it to loop round records on ServerA where the last_update column is >= today's date.
how can i fit it all together including including the loop round ?

thanks

 

[sodakotahusker]

Why do you need to loop? The update section should update all rows which have been altered. To see what would be altered just substitute a select clause right in front of your from and you should see multiple records, all which should be updated when you use the update statement.

 

[richey1]

thanks

i take your point about the loop. I was more wondering about fitting it together as a stored procedure. I've created a linked server between server a and b - the job will run from server a, the update will happen on server b. I know thats worked ok because I've tried a simple select which has worked ok. I'm having problems with my update now though ?
my insert works ok...........

 

[richey1]

this is the update code (the select bit works ok on its own)

use databaseA
update ServerB.EnvSystem.dbo.tblCustomer_ISB
set ServerB.EnvSystem.dbo.tblCustomer_ISB.CustID = vwCustomers.CUSTOMERID

SELECT vwCustomers.CUSTOMERID, vwCustomers.SDATE
FROM ServerB.EnvSystem.dbo.tblCustomer_ISB tblCustomer_ISB_1 INNER JOIN
vwCustomers ON tblCustomer_ISB_1.custID = vwCustomers.CUSTOMERID AND tblCustomer_ISB_1.Sdate <> vwCustomers.SDATE

this is the error
-----------------------------*/
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'vwCustomers' does not match with a table name or alias name used in the query.

 

[Rhys666]

You're not telling the update where vwCustomers.CUSTOMERID is from or how ServerB.EnvSystem.dbo.tblCustomer_ISB relates to it.

UPDATE 
    ServerB.EnvSystem.dbo.tblCustomer_ISB
SET 
    ServerB.EnvSystem.dbo.tblCustomer_ISB.CustID = vwCustomers.CUSTOMERID
FROM
    vwCustomers
WHERE
    ????    --WHERE clause here

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[richey1]

ok so.....

update ServerB.EnvSystem.dbo.tblCustomer_ISB 
set SDate=T2.SDate
from ServerB.EnvSystem.dbo.tblCustomer_ISB T1
inner join vwCustomers T2 on T1.CustID=T2.CustomerID
where T1.SDate <> T2.SDate

works ok but (if i try a multiple set) it fails with error code ? still need it within an SP as well......the insert first else the update else do nothing !

thanks

update ServerB.EnvSystem.dbo.tblCustomer_ISB 
set SDate=T2.SDate,surname=T2.surname
from ServerB.EnvSystem.dbo.tblCustomer_ISB T1
inner join vwCustomers T2 on T1.CustID=T2.CustomerID
where T1.SDate <> T2.SDate

Could not open table '"EnvSystem"."dbo"."tblCustomer_ISB"' from OLE DB provider 'SQLOLEDB'.  The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21:  [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...

 

[sodakotahusker]

for your update statement try using the alias instead of the servername.dbname.tablename

update t1
set field1 ...
from
...

 

[richey1]

sorry for the delay in replying.
the problem was the index on column CustID was not set to unique values.

thanks