I've hit a little problem. I'm Inserting/Updating over 2k of records and within the 2k, there may be some record that I already have. The once I have already I may need to update the original record etc.
The solution uses SQL Compact and it can run on slow computers. I did have a method, but it took over 10 mins. (Way too long).
So now I do a bulk insert into a temp table, then I can do a insert from the temp to the original.
I seem to have an issue with my update script. The below script works with SQL Server, but when I attempt the same script (with the same database schema etc), it does not work.
Can anyone help?
UPDATE Clients
SET Title = Temp_Clients.Title
,Forename = Temp_Clients.Forename
,Surname = Temp_Clients.Surname
,DOB = Temp_Clients.DOB
,IsMale = Temp_Clients.IsMale
,Address1 = Temp_Clients.Address1
,Address2 =Temp_Clients.Address2
,Address3 = Temp_Clients.Address3
,Town = Temp_Clients.Town
,County = Temp_Clients.County
,Postcode = Temp_Clients.Postcode
,Telephone = Temp_Clients.Telephone
,Mobile = Temp_Clients.Mobile
,Email =Temp_Clients.Email
,LocationID = Temp_Clients.LocationID
,GpPractice = Temp_Clients.GpPractice
,GpName = Temp_Clients.GpName
FROM Temp_Clients
INNER JOIN Clients AS A ON A.[CustomClientID] = Temp_Clients.[CustomClientID]
I think your from is the wrong way around: It should be client first then joining on the temp_clients.
UPDATE Clients
SET Title = Temp_Clients.Title
,Forename = Temp_Clients.Forename
,Surname = Temp_Clients.Surname
,DOB = Temp_Clients.DOB
,IsMale = Temp_Clients.IsMale
,Address1 = Temp_Clients.Address1
,Address2 =Temp_Clients.Address2
,Address3 = Temp_Clients.Address3
,Town = Temp_Clients.Town
,County = Temp_Clients.County
,Postcode = Temp_Clients.Postcode
,Telephone = Temp_Clients.Telephone
,Mobile = Temp_Clients.Mobile
,Email =Temp_Clients.Email
,LocationID = Temp_Clients.LocationID
,GpPractice = Temp_Clients.GpPractice
,GpName = Temp_Clients.GpName
FROM Clients INNER JOIN Temp_Clients ON Clients.[CustomClientID] = Temp_Clients.[CustomClientID]