Insert / Update Possible Without Row By Row Processing? 1

[mwolf00]

I have a table that holds data from a linked server. It is updated daily.

[u]tLease[/u]
leaseNum
effDate
expDate

When I update it, I need to add any new leases as well as update any leases where the effective date or expiration date has changed. Is there any way to do this without checking each lease individually? I'm pretty sure that I can add the new leases with a WHERE NOT EXISTS clause like

INSERT tLease 
SELECT leaseNum, effDate, expDate
FROM OPENQUERY(linkCN,'SELECT leaseNum, effDate, expDate FROM otherTable')
WHERE NOT EXISTS (SELECT leaseNum FROM tLease)

Is the syntax correct? Can I do something similar with an UPDATE statement to capture updated dates?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[philhege]

If this table is supposed to be a spot-on copy of the link server table, why not just

DELETE FROM tLease
INSERT tLease
SELECT leaseNum, effDate, expDate
FROM OPENQUERY(linkCN,'SELECT leaseNum, effDate, expDate FROM otherTable')


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[vongrunt]

EXISTS() is not correlated in above code (WHERE clause is missing). Try:

INSERT tLease
SELECT leaseNum, effDate, expDate
FROM OPENQUERY(linkCN,'SELECT leaseNum, effDate, expDate FROM otherTable')
WHERE leaseNum NOT IN (SELECT leaseNum FROM tLease)

Or use EXISTS() or left outer join instead of NOT IN() if you want.

> Can I do something similar with an UPDATE statement to capture updated dates?

Yup:

UPDATE L
SET L.blah = X.blah, ...
FROM tLease L
INNER JOIN OPENQUERY(linkCN,'SELECT leaseNum, effDate, expDate FROM otherTable') X
	ON L.leaseNum = X.leaseNum

Basically... OPENQUERY() behaves as derived table/inline view.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mwolf00]

vongrunt - your update query is failing

UPDATE l
SET l.effDate = dt.effDate
	, l.expDate = dt.expDate
FROM [serverName].dbName.dbo.rrt_tLeaseEff l
INNER JOIN OPENQuery(sybaseCN,'
	SELECT i_lse leaseNumber, 
	convert(char(12), 
		dateadd(day, l.d_lse_st -1 -(floor(l.d_lse_st/1000)*1000),  
		convert(char(4), floor(l.d_lse_st/1000))),1) effDate,
	convert(char(12), 
		dateadd(day, l.d_lse_end -1 -(floor(l.d_lse_end/1000)*1000),  
		convert(char(4), floor(l.d_lse_end/1000))),1) expDate
	FROM tlse l, tbldg b
	WHERE l.c_loc = b.c_loc
	AND b.c_region = ''11''
') dt ON dt.leaseNumber = l.leaseNumber

with this error

Could not open table '"dbName"."dbo"."rrt_tLeaseEff"' 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.]

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[vongrunt]

Does Sybase table have PK/unique index on leaseNumber?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mwolf00]

Yes, I think that the query is not failing on the OPENQUERY part. It seems to be failing on the link to the other SQL Server DB (where rrt_tLeaseEff resides).

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[vongrunt]

Correction: table [serverName].dbName.dbo.rrt_tLeaseEff gets UPDATEd. Does it have PK/unique index defined?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mwolf00]

Doh, that was it! Thank you...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook