Which is fastest? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Which is fastest?

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-21 : 02:24:33

Hi all

we've got some queries where I work that do a delete and then insert operations. Some of the records remain the same after completion, but some will change.

Now, will it be faster to do an update on the records that will change and then insert the remaining or is it quicker to do it the original way?

To give a rough idea of scale, we're talking about 100,000 records a day to delete/insert but only about 10% of those will change.

MartinCroft
Starting Member

4 Posts

Posted - 2011-12-21 : 05:45:24

It might be worth considering using MERGE statement. You didn't stipulate the version of SQL you were using. If using SQL 2008 this is worth considering, with a single pass of the table if the record exists you can do nothing if you choose ( or even update a modified date column if you wanted). If the record didn't exist you can do an insert. So using the MERGE you would only do the inserts and SQL would deal with the rest.

MERGE can be very powerful - some links

http://technet.microsoft.com/en-us/library/bb510625.aspx

http://sqlblogcasts.com/blogs/martincroft/archive/2011/05/07/merge-under-used-statement.aspx

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-21 : 06:02:13

Thanks for that. I am using SQL 2008 but I didn't know aboout that.
So, just to be clear, if a record exists it's updated, if not it's inserted?

MartinCroft
Starting Member

4 Posts

Posted - 2011-12-21 : 06:50:37

You can do either if the record exists ( when matched) you can choose if you want to update or not update ( the choice is yours), dependant on what you want. if it doesn't exist you can insert the new record ( when not matched). Books on line shows some good examples.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-21 : 07:04:36

Ah, got you.
Looks like a bit of research could produce some very good results here.

Thanks for the info.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-22 : 02:44:44

Me again.
I'm trying to write a merge statement and, I must admit, I'm struggling.
I've had a look at the Microsoft Technet page on it and it's left me more confused than ever.

Could you point me to an idiots-guide to writing one please?

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-22 : 03:57:06

OK, got as far as this (it's written as a pass-thorugh query in Access as I've used a linked table for the incoming data.

merge [dbo_NW - DH111_Minimum_Dataset_CAS_Test] as load_table
using (
select mds_record_id
from DH111_Extract)
as from_table
on load_table.mds_record_id=from_table.mds_record_id
when not matched then
insert (mds_Start_Date,
mds_End_Date,
mds_Call_Closed,
mds_Call_Answered,
mds_Call_Taker_Triage_Start,
mds_Call_Taker_Triage_End,
mds_Nurse_Triage_Start,
mds_Nurse_Triage_End,
mds_DDI,mds_DDIe,
mds_Type_of_Phone,
mds_Source_of_Call,
mds_Reason_Not_Triaged,
mds_Transfer,
mds_Call_Back,
mds_Delay_to_Call_Back,
mds_Final_Call_Handler_Disposition,
mds_Final_Nurse_Disposition,
mds_Care_Type,
mds_CMS_Service_Chosen,
mds_Receiver_Skillset,
mds_Record_id,
mds_Priority,
mds_Special_Note_Present,
mds_Postcode,
mds_Gender,
mds_Patient_Age,
mds_GP_Name,
mds_Surgery_Name,
mds_Received_Site,
mds_Nurse_Site,
mds_Life_threat_from_HA,
mds_Life_threat_to_Amb_in_3mins,
mds_Clinical_Area,
mds_First_Pathway_Id,
mds_Second_Pathway_Id,
mds_HA_Disposition,
mds_NA_Disposition,
mds_NurseTransfer_Start,
mds_NurseTransfer_End,
mds_NurseCallBack_Start,
mds_NurseCallBack_End,
PCT_Code,
PCT_Name)
values
(from_table.mds_Start_Date,
from_table.mds_End_Date,
from_table.mds_Call_Closed,
from_table.mds_Call_Answered,
from_table.mds_Call_Taker_Triage_Start,
from_table.mds_Call_Taker_Triage_End,
from_table.mds_Nurse_Triage_Start,
from_table.mds_Nurse_Triage_End,
from_table.mds_DDI,mds_DDIe,
from_table.mds_Type_of_Phone,
from_table.mds_Source_of_Call,
from_table.mds_Reason_Not_Triaged,
from_table.mds_Transfer,
from_table.mds_Call_Back,
from_table.mds_Delay_to_Call_Back,
from_table.mds_Final_Call_Handler_Disposition,
from_table.mds_Final_Nurse_Disposition,
from_table.mds_Care_Type,
from_table.mds_CMS_Service_Chosen,
from_table.mds_Receiver_Skillset,
from_table.mds_Record_id,
from_table.mds_Priority,
from_table.mds_Special_Note_Present,
from_table.mds_Postcode,
from_table.mds_Gender,
from_table.mds_Patient_Age,
from_table.mds_GP_Name,
from_table.mds_Surgery_Name,
from_table.mds_Received_Site,
from_table.mds_Nurse_Site,
from_table.mds_Life_threat_from_HA,
from_table.mds_Life_threat_to_Amb_in_3mins,
from_table.mds_Clinical_Area,
from_table.mds_First_Pathway_Id,
from_table.mds_Second_Pathway_Id,
from_table.mds_HA_Disposition,
from_table.mds_NA_Disposition,
from_table.mds_NurseTransfer_Start,
from_table.mds_NurseTransfer_End,
from_table.mds_NurseCallBack_Start,
from_table.mds_NurseCallBack_End,
from_table.PCT_Code,
from_table.PCT_Name)

when matched then
update
set load_table.mds_Start_Date =from_table.mds_Start_Date,
  load_table.mds_End_Date =from_table.mds_End_Date,
  load_table.mds_Call_Closed =from_table.mds_Call_Closed,
  load_table.mds_Call_Answered =from_table.mds_Call_Answered,
  load_table.mds_Call_Taker_Triage_Start =from_table.mds_Call_Taker_Triage_Start,
  load_table.mds_Call_Taker_Triage_End =from_table.mds_Call_Taker_Triage_End,
  load_table.mds_Nurse_Triage_Start =from_table.mds_Nurse_Triage_Start,
  load_table.mds_Nurse_Triage_End =from_table.mds_Nurse_Triage_End,
  load_table.mds_DDI =from_table.mds_DDI,
  load_table.mds_DDIe =from_table.mds_DDIe,
  load_table.mds_Type_of_Phone =from_table.mds_Type_of_Phone,
  load_table.mds_Source_of_Call =from_table.mds_Source_of_Call,
  load_table.mds_Reason_Not_Triaged =from_table.mds_Reason_Not_Triaged,
  load_table.mds_Transfer =from_table.mds_Transfer,
  load_table.mds_Call_Back =from_table.mds_Call_Back,
  load_table.mds_Delay_to_Call_Back =from_table.mds_Delay_to_Call_Back,
  load_table.mds_Final_Call_Handler_Disposition =from_table.mds_Final_Call_Handler_Disposition,
  load_table.mds_Final_Nurse_Disposition =from_table.mds_Final_Nurse_Disposition,
  load_table.mds_Care_Type =from_table.mds_Care_Type,
  load_table.mds_CMS_Service_Chosen =from_table.mds_CMS_Service_Chosen,
  load_table.mds_Receiver_Skillset =from_table.mds_Receiver_Skillset,
  load_table.mds_Record_id =from_table.mds_Record_id,
  load_table.mds_Priority =from_table.mds_Priority,
  load_table.mds_Special_Note_Present =from_table.mds_Special_Note_Present,
  load_table.mds_Postcode =from_table.mds_Postcode,
  load_table.mds_Gender =from_table.mds_Gender,
  load_table.mds_Patient_Age =from_table.mds_Patient_Age,
  load_table.mds_GP_Name =from_table.mds_GP_Name,
  load_table.mds_Surgery_Name =from_table.mds_Surgery_Name,
  load_table.mds_Received_Site =from_table.mds_Received_Site,
  load_table.mds_Nurse_Site =from_table.mds_Nurse_Site,
  load_table.mds_Life_threat_from_HA =from_table.mds_Life_threat_from_HA,
  load_table.mds_Life_threat_to_Amb_in_3mins =from_table.mds_Life_threat_to_Amb_in_3mins,
  load_table.mds_Clinical_Area =from_table.mds_Clinical_Area,
  load_table.mds_First_Pathway_Id =from_table.mds_First_Pathway_Id,
  load_table.mds_Second_Pathway_Id =from_table.mds_Second_Pathway_Id,
  load_table.mds_HA_Disposition =from_table.mds_HA_Disposition,
  load_table.mds_NA_Disposition =from_table.mds_NA_Disposition,
  load_table.mds_NurseTransfer_Start =from_table.mds_NurseTransfer_Start,
  load_table.mds_NurseTransfer_End =from_table.mds_NurseTransfer_End,
  load_table.mds_NurseCallBack_Start =from_table.mds_NurseCallBack_Start,
  load_table.mds_NurseCallBack_End =from_table.mds_NurseCallBack_End,
  load_table.PCT_Code =from_table.PCT_Code,
  load_table.PCT_Name=from_table.PCT_Name;

I'm getting an error saying invalid object name 'DH111_Extract'.
That's the name of my linked table I'll be insert/updating from.
Anyone any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-22 : 06:34:05

Your "from_table" is only exposing the mds_record_id column.
Try this

MERGE	[dbo_NW - DH111_Minimum_Dataset_CAS_Test] AS load_table
USING	DH111_Extract AS from_table ON from_table.mds_record_id = tgt.mds_record_id
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					mds_Start_Date,
					mds_End_Date,
					mds_Call_Closed,
					mds_Call_Answered,
					mds_Call_Taker_Triage_Start,
					mds_Call_Taker_Triage_End,
					mds_Nurse_Triage_Start,
					mds_Nurse_Triage_End,
					mds_DDI,
					mds_DDIe,
					mds_Type_of_Phone,
					mds_Source_of_Call,
					mds_Reason_Not_Triaged,
					mds_Transfer,
					mds_Call_Back,
					mds_Delay_to_Call_Back,
					mds_Final_Call_Handler_Disposition,
					mds_Final_Nurse_Disposition,
					mds_Care_Type,
					mds_CMS_Service_Chosen,
					mds_Receiver_Skillset,
					mds_Record_id,
					mds_Priority,
					mds_Special_Note_Present,
					mds_Postcode,
					mds_Gender,
					mds_Patient_Age,
					mds_GP_Name,
					mds_Surgery_Name,
					mds_Received_Site,
					mds_Nurse_Site,
					mds_Life_threat_from_HA,
					mds_Life_threat_to_Amb_in_3mins,
					mds_Clinical_Area,
					mds_First_Pathway_Id,
					mds_Second_Pathway_Id,
					mds_HA_Disposition,
					mds_NA_Disposition,
					mds_NurseTransfer_Start,
					mds_NurseTransfer_End,
					mds_NurseCallBack_Start,
					mds_NurseCallBack_End,
					PCT_Code,
					PCT_Name
				)
			VALUES	(
					from_table.mds_Start_Date,
					from_table.mds_End_Date,
					from_table.mds_Call_Closed,
					from_table.mds_Call_Answered,
					from_table.mds_Call_Taker_Triage_Start,
					from_table.mds_Call_Taker_Triage_End,
					from_table.mds_Nurse_Triage_Start,
					from_table.mds_Nurse_Triage_End,
					from_table.mds_DDI,mds_DDIe,
					from_table.mds_Type_of_Phone,
					from_table.mds_Source_of_Call,
					from_table.mds_Reason_Not_Triaged,
					from_table.mds_Transfer,
					from_table.mds_Call_Back,
					from_table.mds_Delay_to_Call_Back,
					from_table.mds_Final_Call_Handler_Disposition,
					from_table.mds_Final_Nurse_Disposition,
					from_table.mds_Care_Type,
					from_table.mds_CMS_Service_Chosen,
					from_table.mds_Receiver_Skillset,
					from_table.mds_Record_id,
					from_table.mds_Priority,
					from_table.mds_Special_Note_Present,
					from_table.mds_Postcode,
					from_table.mds_Gender,
					from_table.mds_Patient_Age,
					from_table.mds_GP_Name,
					from_table.mds_Surgery_Name,
					from_table.mds_Received_Site,
					from_table.mds_Nurse_Site,
					from_table.mds_Life_threat_from_HA,
					from_table.mds_Life_threat_to_Amb_in_3mins,
					from_table.mds_Clinical_Area,
					from_table.mds_First_Pathway_Id,
					from_table.mds_Second_Pathway_Id,
					from_table.mds_HA_Disposition,
					from_table.mds_NA_Disposition,
					from_table.mds_NurseTransfer_Start,
					from_table.mds_NurseTransfer_End,
					from_table.mds_NurseCallBack_Start,
					from_table.mds_NurseCallBack_End,
					from_table.PCT_Code,
					from_table.PCT_Name
				)
WHEN	MATCHED
		THEN	UPDATE
			SET	load_table.mds_Start_Date = from_table.mds_Start_Date,
				load_table.mds_End_Date = from_table.mds_End_Date,
				load_table.mds_Call_Closed = from_table.mds_Call_Closed,
				load_table.mds_Call_Answered = from_table.mds_Call_Answered,
				load_table.mds_Call_Taker_Triage_Start = from_table.mds_Call_Taker_Triage_Start,
				load_table.mds_Call_Taker_Triage_End =from_table.mds_Call_Taker_Triage_End,
				load_table.mds_Nurse_Triage_Start = from_table.mds_Nurse_Triage_Start,
				load_table.mds_Nurse_Triage_End = from_table.mds_Nurse_Triage_End,
				load_table.mds_DDI = from_table.mds_DDI,
				load_table.mds_DDIe = from_table.mds_DDIe,
				load_table.mds_Type_of_Phone = from_table.mds_Type_of_Phone,
				load_table.mds_Source_of_Call = from_table.mds_Source_of_Call,
				load_table.mds_Reason_Not_Triaged = from_table.mds_Reason_Not_Triaged,
				load_table.mds_Transfer = from_table.mds_Transfer,
				load_table.mds_Call_Back = from_table.mds_Call_Back,
				load_table.mds_Delay_to_Call_Back = from_table.mds_Delay_to_Call_Back,
				load_table.mds_Final_Call_Handler_Disposition = from_table.mds_Final_Call_Handler_Disposition,
				load_table.mds_Final_Nurse_Disposition = from_table.mds_Final_Nurse_Disposition,
				load_table.mds_Care_Type = from_table.mds_Care_Type,
				load_table.mds_CMS_Service_Chosen = from_table.mds_CMS_Service_Chosen,
				load_table.mds_Receiver_Skillset = from_table.mds_Receiver_Skillset,
				load_table.mds_Record_id = from_table.mds_Record_id,
				load_table.mds_Priority = from_table.mds_Priority,
				load_table.mds_Special_Note_Present = from_table.mds_Special_Note_Present,
				load_table.mds_Postcode = from_table.mds_Postcode,
				load_table.mds_Gender = from_table.mds_Gender,
				load_table.mds_Patient_Age = from_table.mds_Patient_Age,
				load_table.mds_GP_Name = from_table.mds_GP_Name,
				load_table.mds_Surgery_Name = from_table.mds_Surgery_Name,
				load_table.mds_Received_Site = from_table.mds_Received_Site,
				load_table.mds_Nurse_Site = from_table.mds_Nurse_Site,
				load_table.mds_Life_threat_from_HA = from_table.mds_Life_threat_from_HA,
				load_table.mds_Life_threat_to_Amb_in_3mins = from_table.mds_Life_threat_to_Amb_in_3mins,
				load_table.mds_Clinical_Area = from_table.mds_Clinical_Area,
				load_table.mds_First_Pathway_Id = from_table.mds_First_Pathway_Id,
				load_table.mds_Second_Pathway_Id = from_table.mds_Second_Pathway_Id,
				load_table.mds_HA_Disposition = from_table.mds_HA_Disposition,
				load_table.mds_NA_Disposition = from_table.mds_NA_Disposition,
				load_table.mds_NurseTransfer_Start = from_table.mds_NurseTransfer_Start,
				load_table.mds_NurseTransfer_End = from_table.mds_NurseTransfer_End,
				load_table.mds_NurseCallBack_Start = from_table.mds_NurseCallBack_Start,
				load_table.mds_NurseCallBack_End = from_table.mds_NurseCallBack_End,
				load_table.PCT_Code = from_table.PCT_Code,
				load_table.PCT_Name = from_table.PCT_Name;

N 56°04'39.26"
E 12°55'05.63"

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-27 : 03:40:36

Morning all

Just tried your updated SQL and I'm getting an error.
The error says "Invalid object name DH111_Extract".

I'm assuming that it's because this is a table linked to a text file, dbo_NW - DH111_Minimum_Dataset_CAS_Test is linked to a file n a server and the whole thing is trying to be done via Access using pass-through (or variants) queries.

Anyone any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 04:51:55

so are data coming from files and not from tables? then you need add a linked server connection to them and then use linkedserver...[textfilename]
or use OPENROWSET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-27 : 05:32:17

Erm ..... pardon?
I'm fairly new to this so can I have some idiot-proof instructions please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 06:37:00

see

http://www.sql-programmers.com/Blog/tabid/153/EntryId/49/TSQL-OPENROWSET-in-SQL-Server.aspx

http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-12-27 : 16:32:07

Using MERGE or Update/Insert (often named "Upsert") is up to you (others can help you with syntax for MERGE, its not something I know about), however I just wanted to make this point:

You say that "Some of the records remain the same after completion" and I think it is important that these are neither Deleted/Re-Insert nor Updated. Both have implications:

Using Delete/Insert will mean that the records are missing for a period of time. You can use a TRANSACTION to ensure that it happens as an ATOMIC block, but some twit will create queries with NOLOCK hint and then they will be missing from some senior managers report ...

Even without that risk you are causing a lot of transaction "effort", and indexes being deleted, and then recreated.

Even using UPDATE of records where there is no substantive change means that there is transaction "effort", and if there are triggers on the table (for example to record Audit Changes) there is more "effort" - plus the confusion of having rows in the Audit Log where nothing actually changed.

We do:


-- Update if changed
UPDATE D
SET D.Col1 = S.Col1,
    ....
FROM SourceTable AS S
     JOIN DestinationTable AS D
         ON D.ID = S.ID
WHERE    (D.[Col1] COLLATE Latin1_General_BIN2 <> S.[Col1] -- leave out the COLLATE on non-Char columns 
             OR (D.[Col1] IS NULL AND S.[Col1] IS NOT NULL) OR (D.[Col1] IS NOT NULL AND S.[Col1] IS NULL))
      OR (D.[Col2] ...

-- Insert if New
INSERT INTO DestinationTable(Col1, ...)
SELECT S.Col1, ...
FROM SourceTable AS S
     LEFT OUTER JOIN DestinationTable AS D
         ON D.ID = S.ID
WHERE D.ID IS NULL

-- Delete if no longer exists in Source Table
DELETE D
FROM DestinationTable AS D
     LEFT OUTER JOIN SourceTable AS S
         ON S.ID = D.ID
WHERE S.ID IS NULL

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-28 : 03:36:57

OK, I'm trying to use OPENROWSET in my MERGE statement, but it's not going well.
This is my code so far:-
MERGE [dbo_NW - DH111_Minimum_Dataset_CAS_Test] AS load_table
USING openrowset ('W:\National\CIT\Team\Projects\111\Bids\NW 111\FCMS Data\DH111_Extract.txt') from_table ON from_table.mds_record_id = load_table.mds_record_id
WHEN NOT MATCHED BY TARGET
THEN INSERT (
mds_Start_Date,
mds_End_Date,
mds_Call_Closed,
mds_Call_Answered,
mds_Call_Taker_Triage_Start,
mds_Call_Taker_Triage_End,
mds_Nurse_Triage_Start,
mds_Nurse_Triage_End,
mds_DDI,
mds_DDIe,
mds_Type_of_Phone,
mds_Source_of_Call,
mds_Reason_Not_Triaged,
mds_Transfer,
mds_Call_Back,
mds_Delay_to_Call_Back,
mds_Final_Call_Handler_Disposition,
mds_Final_Nurse_Disposition,
mds_Care_Type,
mds_CMS_Service_Chosen,
mds_Receiver_Skillset,
mds_Record_id,
mds_Priority,
mds_Special_Note_Present,
mds_Postcode,
mds_Gender,
mds_Patient_Age,
mds_GP_Name,
mds_Surgery_Name,
mds_Received_Site,
mds_Nurse_Site,
mds_Life_threat_from_HA,
mds_Life_threat_to_Amb_in_3mins,
mds_Clinical_Area,
mds_First_Pathway_Id,
mds_Second_Pathway_Id,
mds_HA_Disposition,
mds_NA_Disposition,
mds_NurseTransfer_Start,
mds_NurseTransfer_End,
mds_NurseCallBack_Start,
mds_NurseCallBack_End,
PCT_Code,
PCT_Name
)
VALUES (
from_table.mds_Start_Date,
from_table.mds_End_Date,
from_table.mds_Call_Closed,
from_table.mds_Call_Answered,
from_table.mds_Call_Taker_Triage_Start,
from_table.mds_Call_Taker_Triage_End,
from_table.mds_Nurse_Triage_Start,
from_table.mds_Nurse_Triage_End,
from_table.mds_DDI,mds_DDIe,
from_table.mds_Type_of_Phone,
from_table.mds_Source_of_Call,
from_table.mds_Reason_Not_Triaged,
from_table.mds_Transfer,
from_table.mds_Call_Back,
from_table.mds_Delay_to_Call_Back,
from_table.mds_Final_Call_Handler_Disposition,
from_table.mds_Final_Nurse_Disposition,
from_table.mds_Care_Type,
from_table.mds_CMS_Service_Chosen,
from_table.mds_Receiver_Skillset,
from_table.mds_Record_id,
from_table.mds_Priority,
from_table.mds_Special_Note_Present,
from_table.mds_Postcode,
from_table.mds_Gender,
from_table.mds_Patient_Age,
from_table.mds_GP_Name,
from_table.mds_Surgery_Name,
from_table.mds_Received_Site,
from_table.mds_Nurse_Site,
from_table.mds_Life_threat_from_HA,
from_table.mds_Life_threat_to_Amb_in_3mins,
from_table.mds_Clinical_Area,
from_table.mds_First_Pathway_Id,
from_table.mds_Second_Pathway_Id,
from_table.mds_HA_Disposition,
from_table.mds_NA_Disposition,
from_table.mds_NurseTransfer_Start,
from_table.mds_NurseTransfer_End,
from_table.mds_NurseCallBack_Start,
from_table.mds_NurseCallBack_End,
from_table.PCT_Code,
from_table.PCT_Name
)
WHEN MATCHED
THEN UPDATE
SET load_table.mds_Start_Date = from_table.mds_Start_Date,
load_table.mds_End_Date = from_table.mds_End_Date,
load_table.mds_Call_Closed = from_table.mds_Call_Closed,
load_table.mds_Call_Answered = from_table.mds_Call_Answered,
load_table.mds_Call_Taker_Triage_Start = from_table.mds_Call_Taker_Triage_Start,
load_table.mds_Call_Taker_Triage_End =from_table.mds_Call_Taker_Triage_End,
load_table.mds_Nurse_Triage_Start = from_table.mds_Nurse_Triage_Start,
load_table.mds_Nurse_Triage_End = from_table.mds_Nurse_Triage_End,
load_table.mds_DDI = from_table.mds_DDI,
load_table.mds_DDIe = from_table.mds_DDIe,
load_table.mds_Type_of_Phone = from_table.mds_Type_of_Phone,
load_table.mds_Source_of_Call = from_table.mds_Source_of_Call,
load_table.mds_Reason_Not_Triaged = from_table.mds_Reason_Not_Triaged,
load_table.mds_Transfer = from_table.mds_Transfer,
load_table.mds_Call_Back = from_table.mds_Call_Back,
load_table.mds_Delay_to_Call_Back = from_table.mds_Delay_to_Call_Back,
load_table.mds_Final_Call_Handler_Disposition = from_table.mds_Final_Call_Handler_Disposition,
load_table.mds_Final_Nurse_Disposition = from_table.mds_Final_Nurse_Disposition,
load_table.mds_Care_Type = from_table.mds_Care_Type,
load_table.mds_CMS_Service_Chosen = from_table.mds_CMS_Service_Chosen,
load_table.mds_Receiver_Skillset = from_table.mds_Receiver_Skillset,
load_table.mds_Record_id = from_table.mds_Record_id,
load_table.mds_Priority = from_table.mds_Priority,
load_table.mds_Special_Note_Present = from_table.mds_Special_Note_Present,
load_table.mds_Postcode = from_table.mds_Postcode,
load_table.mds_Gender = from_table.mds_Gender,
load_table.mds_Patient_Age = from_table.mds_Patient_Age,
load_table.mds_GP_Name = from_table.mds_GP_Name,
load_table.mds_Surgery_Name = from_table.mds_Surgery_Name,
load_table.mds_Received_Site = from_table.mds_Received_Site,
load_table.mds_Nurse_Site = from_table.mds_Nurse_Site,
load_table.mds_Life_threat_from_HA = from_table.mds_Life_threat_from_HA,
load_table.mds_Life_threat_to_Amb_in_3mins = from_table.mds_Life_threat_to_Amb_in_3mins,
load_table.mds_Clinical_Area = from_table.mds_Clinical_Area,
load_table.mds_First_Pathway_Id = from_table.mds_First_Pathway_Id,
load_table.mds_Second_Pathway_Id = from_table.mds_Second_Pathway_Id,
load_table.mds_HA_Disposition = from_table.mds_HA_Disposition,
load_table.mds_NA_Disposition = from_table.mds_NA_Disposition,
load_table.mds_NurseTransfer_Start = from_table.mds_NurseTransfer_Start,
load_table.mds_NurseTransfer_End = from_table.mds_NurseTransfer_End,
load_table.mds_NurseCallBack_Start = from_table.mds_NurseCallBack_Start,
load_table.mds_NurseCallBack_End = from_table.mds_NurseCallBack_End,
load_table.PCT_Code = from_table.PCT_Code,
load_table.PCT_Name = from_table.PCT_Name;

I can't use the BULK operator as I don't have permission, and when I try to run the above, I get an error about a ")" somewhere.

Anyone any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:44:34

your usage of OPENROWSET is not correct. you've not specified provider information. see link posted and use correct syntax

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-28 : 04:22:35

This is my latest code:-
MERGE sandpit.dbo.[NW - DH111_Minimum_Dataset_CAS_Test] AS load_table
USING openrowset ('Microsoft.Jet.OLEDB.4.0','Text;Database=W:\National\CIT\Team\Projects\111\Bids\NW 111\FCMS Data\DH111_Extract.txt') from_table ON from_table.mds_record_id = load_table.mds_record_id

WHEN NOT MATCHED BY TARGET
THEN INSERT (
mds_Start_Date,
mds_End_Date,
mds_Call_Closed,
mds_Call_Answered,
mds_Call_Taker_Triage_Start,
mds_Call_Taker_Triage_End,
mds_Nurse_Triage_Start,
mds_Nurse_Triage_End,
mds_DDI,
mds_DDIe,
mds_Type_of_Phone,
mds_Source_of_Call,
mds_Reason_Not_Triaged,
mds_Transfer,
mds_Call_Back,
mds_Delay_to_Call_Back,
mds_Final_Call_Handler_Disposition,
mds_Final_Nurse_Disposition,
mds_Care_Type,
mds_CMS_Service_Chosen,
mds_Receiver_Skillset,
mds_Record_id,
mds_Priority,
mds_Special_Note_Present,
mds_Postcode,
mds_Gender,
mds_Patient_Age,
mds_GP_Name,
mds_Surgery_Name,
mds_Received_Site,
mds_Nurse_Site,
mds_Life_threat_from_HA,
mds_Life_threat_to_Amb_in_3mins,
mds_Clinical_Area,
mds_First_Pathway_Id,
mds_Second_Pathway_Id,
mds_HA_Disposition,
mds_NA_Disposition,
mds_NurseTransfer_Start,
mds_NurseTransfer_End,
mds_NurseCallBack_Start,
mds_NurseCallBack_End,
PCT_Code,
PCT_Name
)
VALUES (
from_table.mds_Start_Date,
from_table.mds_End_Date,
from_table.mds_Call_Closed,
from_table.mds_Call_Answered,
from_table.mds_Call_Taker_Triage_Start,
from_table.mds_Call_Taker_Triage_End,
from_table.mds_Nurse_Triage_Start,
from_table.mds_Nurse_Triage_End,
from_table.mds_DDI,mds_DDIe,
from_table.mds_Type_of_Phone,
from_table.mds_Source_of_Call,
from_table.mds_Reason_Not_Triaged,
from_table.mds_Transfer,
from_table.mds_Call_Back,
from_table.mds_Delay_to_Call_Back,
from_table.mds_Final_Call_Handler_Disposition,
from_table.mds_Final_Nurse_Disposition,
from_table.mds_Care_Type,
from_table.mds_CMS_Service_Chosen,
from_table.mds_Receiver_Skillset,
from_table.mds_Record_id,
from_table.mds_Priority,
from_table.mds_Special_Note_Present,
from_table.mds_Postcode,
from_table.mds_Gender,
from_table.mds_Patient_Age,
from_table.mds_GP_Name,
from_table.mds_Surgery_Name,
from_table.mds_Received_Site,
from_table.mds_Nurse_Site,
from_table.mds_Life_threat_from_HA,
from_table.mds_Life_threat_to_Amb_in_3mins,
from_table.mds_Clinical_Area,
from_table.mds_First_Pathway_Id,
from_table.mds_Second_Pathway_Id,
from_table.mds_HA_Disposition,
from_table.mds_NA_Disposition,
from_table.mds_NurseTransfer_Start,
from_table.mds_NurseTransfer_End,
from_table.mds_NurseCallBack_Start,
from_table.mds_NurseCallBack_End,
from_table.PCT_Code,
from_table.PCT_Name
)
WHEN MATCHED
THEN UPDATE
SET load_table.mds_Start_Date = from_table.mds_Start_Date,
load_table.mds_End_Date = from_table.mds_End_Date,
load_table.mds_Call_Closed = from_table.mds_Call_Closed,
load_table.mds_Call_Answered = from_table.mds_Call_Answered,
load_table.mds_Call_Taker_Triage_Start = from_table.mds_Call_Taker_Triage_Start,
load_table.mds_Call_Taker_Triage_End =from_table.mds_Call_Taker_Triage_End,
load_table.mds_Nurse_Triage_Start = from_table.mds_Nurse_Triage_Start,
load_table.mds_Nurse_Triage_End = from_table.mds_Nurse_Triage_End,
load_table.mds_DDI = from_table.mds_DDI,
load_table.mds_DDIe = from_table.mds_DDIe,
load_table.mds_Type_of_Phone = from_table.mds_Type_of_Phone,
load_table.mds_Source_of_Call = from_table.mds_Source_of_Call,
load_table.mds_Reason_Not_Triaged = from_table.mds_Reason_Not_Triaged,
load_table.mds_Transfer = from_table.mds_Transfer,
load_table.mds_Call_Back = from_table.mds_Call_Back,
load_table.mds_Delay_to_Call_Back = from_table.mds_Delay_to_Call_Back,
load_table.mds_Final_Call_Handler_Disposition = from_table.mds_Final_Call_Handler_Disposition,
load_table.mds_Final_Nurse_Disposition = from_table.mds_Final_Nurse_Disposition,
load_table.mds_Care_Type = from_table.mds_Care_Type,
load_table.mds_CMS_Service_Chosen = from_table.mds_CMS_Service_Chosen,
load_table.mds_Receiver_Skillset = from_table.mds_Receiver_Skillset,
load_table.mds_Record_id = from_table.mds_Record_id,
load_table.mds_Priority = from_table.mds_Priority,
load_table.mds_Special_Note_Present = from_table.mds_Special_Note_Present,
load_table.mds_Postcode = from_table.mds_Postcode,
load_table.mds_Gender = from_table.mds_Gender,
load_table.mds_Patient_Age = from_table.mds_Patient_Age,
load_table.mds_GP_Name = from_table.mds_GP_Name,
load_table.mds_Surgery_Name = from_table.mds_Surgery_Name,
load_table.mds_Received_Site = from_table.mds_Received_Site,
load_table.mds_Nurse_Site = from_table.mds_Nurse_Site,
load_table.mds_Life_threat_from_HA = from_table.mds_Life_threat_from_HA,
load_table.mds_Life_threat_to_Amb_in_3mins = from_table.mds_Life_threat_to_Amb_in_3mins,
load_table.mds_Clinical_Area = from_table.mds_Clinical_Area,
load_table.mds_First_Pathway_Id = from_table.mds_First_Pathway_Id,
load_table.mds_Second_Pathway_Id = from_table.mds_Second_Pathway_Id,
load_table.mds_HA_Disposition = from_table.mds_HA_Disposition,
load_table.mds_NA_Disposition = from_table.mds_NA_Disposition,
load_table.mds_NurseTransfer_Start = from_table.mds_NurseTransfer_Start,
load_table.mds_NurseTransfer_End = from_table.mds_NurseTransfer_End,
load_table.mds_NurseCallBack_Start = from_table.mds_NurseCallBack_Start,
load_table.mds_NurseCallBack_End = from_table.mds_NurseCallBack_End,
load_table.PCT_Code = from_table.PCT_Code,
load_table.PCT_Name = from_table.PCT_Name;

I'm still getting the same error about the ")" though.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-28 : 04:36:57

OK, sorted the syntax (I think) and got this:-
MERGE sandpit.dbo.[NW - DH111_Minimum_Dataset_CAS_Test] AS load_table
USING openrowset ('Microsoft.Jet.OLEDB.4.0','Text;Database=W:\National\CIT\Team\Projects\111\Bids\NW 111\FCMS Data\DH111_Extract.txt',single_blob) from_table ON from_table.mds_record_id = load_table.mds_record_id
WHEN NOT MATCHED BY TARGET
THEN INSERT (
mds_Start_Date,
mds_End_Date,
mds_Call_Closed,
mds_Call_Answered,
mds_Call_Taker_Triage_Start,
mds_Call_Taker_Triage_End,
mds_Nurse_Triage_Start,
mds_Nurse_Triage_End,
mds_DDI,
mds_DDIe,
mds_Type_of_Phone,
mds_Source_of_Call,
mds_Reason_Not_Triaged,
mds_Transfer,
mds_Call_Back,
mds_Delay_to_Call_Back,
mds_Final_Call_Handler_Disposition,
mds_Final_Nurse_Disposition,
mds_Care_Type,
mds_CMS_Service_Chosen,
mds_Receiver_Skillset,
mds_Record_id,
mds_Priority,
mds_Special_Note_Present,
mds_Postcode,
mds_Gender,
mds_Patient_Age,
mds_GP_Name,
mds_Surgery_Name,
mds_Received_Site,
mds_Nurse_Site,
mds_Life_threat_from_HA,
mds_Life_threat_to_Amb_in_3mins,
mds_Clinical_Area,
mds_First_Pathway_Id,
mds_Second_Pathway_Id,
mds_HA_Disposition,
mds_NA_Disposition,
mds_NurseTransfer_Start,
mds_NurseTransfer_End,
mds_NurseCallBack_Start,
mds_NurseCallBack_End,
PCT_Code,
PCT_Name
)
VALUES (
from_table.mds_Start_Date,
from_table.mds_End_Date,
from_table.mds_Call_Closed,
from_table.mds_Call_Answered,
from_table.mds_Call_Taker_Triage_Start,
from_table.mds_Call_Taker_Triage_End,
from_table.mds_Nurse_Triage_Start,
from_table.mds_Nurse_Triage_End,
from_table.mds_DDI,mds_DDIe,
from_table.mds_Type_of_Phone,
from_table.mds_Source_of_Call,
from_table.mds_Reason_Not_Triaged,
from_table.mds_Transfer,
from_table.mds_Call_Back,
from_table.mds_Delay_to_Call_Back,
from_table.mds_Final_Call_Handler_Disposition,
from_table.mds_Final_Nurse_Disposition,
from_table.mds_Care_Type,
from_table.mds_CMS_Service_Chosen,
from_table.mds_Receiver_Skillset,
from_table.mds_Record_id,
from_table.mds_Priority,
from_table.mds_Special_Note_Present,
from_table.mds_Postcode,
from_table.mds_Gender,
from_table.mds_Patient_Age,
from_table.mds_GP_Name,
from_table.mds_Surgery_Name,
from_table.mds_Received_Site,
from_table.mds_Nurse_Site,
from_table.mds_Life_threat_from_HA,
from_table.mds_Life_threat_to_Amb_in_3mins,
from_table.mds_Clinical_Area,
from_table.mds_First_Pathway_Id,
from_table.mds_Second_Pathway_Id,
from_table.mds_HA_Disposition,
from_table.mds_NA_Disposition,
from_table.mds_NurseTransfer_Start,
from_table.mds_NurseTransfer_End,
from_table.mds_NurseCallBack_Start,
from_table.mds_NurseCallBack_End,
from_table.PCT_Code,
from_table.PCT_Name
)
WHEN MATCHED
THEN UPDATE
SET load_table.mds_Start_Date = from_table.mds_Start_Date,
load_table.mds_End_Date = from_table.mds_End_Date,
load_table.mds_Call_Closed = from_table.mds_Call_Closed,
load_table.mds_Call_Answered = from_table.mds_Call_Answered,
load_table.mds_Call_Taker_Triage_Start = from_table.mds_Call_Taker_Triage_Start,
load_table.mds_Call_Taker_Triage_End =from_table.mds_Call_Taker_Triage_End,
load_table.mds_Nurse_Triage_Start = from_table.mds_Nurse_Triage_Start,
load_table.mds_Nurse_Triage_End = from_table.mds_Nurse_Triage_End,
load_table.mds_DDI = from_table.mds_DDI,
load_table.mds_DDIe = from_table.mds_DDIe,
load_table.mds_Type_of_Phone = from_table.mds_Type_of_Phone,
load_table.mds_Source_of_Call = from_table.mds_Source_of_Call,
load_table.mds_Reason_Not_Triaged = from_table.mds_Reason_Not_Triaged,
load_table.mds_Transfer = from_table.mds_Transfer,
load_table.mds_Call_Back = from_table.mds_Call_Back,
load_table.mds_Delay_to_Call_Back = from_table.mds_Delay_to_Call_Back,
load_table.mds_Final_Call_Handler_Disposition = from_table.mds_Final_Call_Handler_Disposition,
load_table.mds_Final_Nurse_Disposition = from_table.mds_Final_Nurse_Disposition,
load_table.mds_Care_Type = from_table.mds_Care_Type,
load_table.mds_CMS_Service_Chosen = from_table.mds_CMS_Service_Chosen,
load_table.mds_Receiver_Skillset = from_table.mds_Receiver_Skillset,
load_table.mds_Record_id = from_table.mds_Record_id,
load_table.mds_Priority = from_table.mds_Priority,
load_table.mds_Special_Note_Present = from_table.mds_Special_Note_Present,
load_table.mds_Postcode = from_table.mds_Postcode,
load_table.mds_Gender = from_table.mds_Gender,
load_table.mds_Patient_Age = from_table.mds_Patient_Age,
load_table.mds_GP_Name = from_table.mds_GP_Name,
load_table.mds_Surgery_Name = from_table.mds_Surgery_Name,
load_table.mds_Received_Site = from_table.mds_Received_Site,
load_table.mds_Nurse_Site = from_table.mds_Nurse_Site,
load_table.mds_Life_threat_from_HA = from_table.mds_Life_threat_from_HA,
load_table.mds_Life_threat_to_Amb_in_3mins = from_table.mds_Life_threat_to_Amb_in_3mins,
load_table.mds_Clinical_Area = from_table.mds_Clinical_Area,
load_table.mds_First_Pathway_Id = from_table.mds_First_Pathway_Id,
load_table.mds_Second_Pathway_Id = from_table.mds_Second_Pathway_Id,
load_table.mds_HA_Disposition = from_table.mds_HA_Disposition,
load_table.mds_NA_Disposition = from_table.mds_NA_Disposition,
load_table.mds_NurseTransfer_Start = from_table.mds_NurseTransfer_Start,
load_table.mds_NurseTransfer_End = from_table.mds_NurseTransfer_End,
load_table.mds_NurseCallBack_Start = from_table.mds_NurseCallBack_Start,
load_table.mds_NurseCallBack_End = from_table.mds_NurseCallBack_End,
load_table.PCT_Code = from_table.PCT_Code,
load_table.PCT_Name = from_table.PCT_Name;

I'm now getting the following error:-
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Any help greatly appreciated.

sureshkk
Starting Member

21 Posts

Posted - 2011-12-28 : 05:13:28

enable the OpenRowSet feature using following commands.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Note: Make sure the file is on server.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-28 : 05:33:16

quote:
Originally posted by sureshkk

Note: Make sure the file is on server.

When you say server, which one do you mean?
The files are kept on a network drive, but the SQL server is a different server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 07:23:22

quote:
Originally posted by rmg1

quote:
Originally posted by sureshkk

Note: Make sure the file is on server.

When you say server, which one do you mean?
The files are kept on a network drive, but the SQL server is a different server.

then specify a UNC path or mapped path

is W: mapped path drive? or is it absolute path in file server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-28 : 07:55:36

W: is a mapped drive that is the same on all relevant machines.
It's based on the user so if you're not supposed to access it, you can't see it.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources