VERY URGENT SQL 2000 PROBLEM!

[JCAD1]

I have got two tables. Table1 is a temporary table where data is gathered, manipulated and used to update table2 (i.e. after data is manipulated, it is copied across to table2 where it’s stored permanently). Here is the code for creating table1:

create table table1 (
	newid varchar (50),
	id varchar (50),
	prov varchar (5),
	purch varchar (5),
	serial varchar (17),
	ctrline varchar (17),
	nhsnum varchar (17),
	nhsstat varchar (2),
	surname varchar (30),
	forename varchar (30),
	postcode varchar (7),
	dha varchar (3),
	pct varchar (8),
	Sex varchar (1),
	dob varchar (10) ,
	unitnum varchar (10),
	spellnum varchar (40),
	epnum varchar (17),
	startdate varchar (10),
	enddate varchar (10),
	spec varchar (3),
	speccons varchar (3),
	specloc varchar (5),
	cons varchar (10),
	cdsid varchar (35),
	actmnth varchar (7)
)

There are two id (key) fields which are calculated as follows:

update table1
set newid = cdsid + epnum + prov 

Update table1
Set id = prov + startDate + 
(case when EndDate is null then '?' 
	else EndDate
	end) + epnum + Unitnum + Spec + Cons

The rule is that users should use newid at all times when querying the tables. However, if cdsid is null then they can use id instead. Besides, table1 is in a database called hnatemp which is located on server1. Coversely, table2 is in a database called hnalive which is on server2. I want to look for a record(s) in table2 matching id and overwrite old record(s) in table2 with new ones from table1. If no match is found, I want it to create/insert a new record(s) in table2. How do I do this in SQL 2000? Is it possible to get the number of records overwritten, number of records added and the total number of records in table2?

I have tried using the code below to remove old records before adding in new ones:

delete d
from hnatemp..table1 e
join server2.hnalive.dbo.table2 d 
on d.ID = e.ID

but it keeps coming up with the following errors every time I run the procedure:

“Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"hnalive"."dbo"."table2"' 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...”

Could someone please write me a code which can help me to achieve what I am trying to do?

The other problem I am having is that sometimes both newid and id keys are not unique. In other words, I get duplicate newid and id values in the records. There may be four duplicate records and sometimes I may only need the first record, other times I may need the second or the third or the fourth record. How do I get rid of the records I do not want, leaving just the one I am interested in in the table?

Thank you in advance for your help.

Jcad1

 

[SQLSister]

YOU have so many problems it's hard to know where to start.

First you never create tables to be used temporarlity as real tables, that's what temp tables are for. Read about how to create them in Books on line.

Second, What you want to do is create a stored procedure that Creates and populates the temp table and in the process does any data manipulations you want to do. Then you need to run two statements, one to update existing record and one to insert new ones. However to do this effectively, you need to define a unique key. Until you have a way of telling which records are in the database and which are not then this process cannot work. All this should be wrapped in transactions.

You need to look up how to do insert and update statements using joins with other tables. You will want to use a lft join for the insert statment. Books online is your friend, you need to start using it.