June 15, 2012 at 9:29 am
Please help me to find the error when I use merge it work but when I add the insert into statement I get an error.
below is my store procedure.
Thanks
error....
Msg 102, Level 15, State 1, Procedure CreateHobson_Merge1, Line 77
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Procedure CreateHobson_Merge1, Line 96
Incorrect syntax near the keyword 'AS'
----
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateHobson_Merge1]
AS
BEGIN
SET NOCOUNT ON;
declare @cnt as int;
declare @yrtr as varchar(5);
declare @CurrentTime as datetime2;
set @CurrentTime =getdate();
INSERT INTO dbo.tblhobsonDailysend
SELECT tech_id, last_name, first_name, middle_name, name_suff,app_begin_date,
FT_PT, adm_stat, app_type_code, military_serv_flag, ethnic_code,
death_date, Personalemail, Studentemail, immun_code, phone, street1, street2, city,
state_of_resident, zip, MNCounty, cell_phone,
hs_code,hs_grad_date, o_hs_transcript_Rcvd,
ACC_Reading, ACC_Sentence, ACC_Arithmetic, ACC_Elem_Algebra, ACC_Coll_Math,
Waiver_Math, Waiver_Eng, Waiver_Read, Waiver_ASLS, Waiver_OESL,
inst_name_1, inst_act_code_1, inst_offcl_trnsc_date_1, inst_deg_earned_1,
inst_name_2, inst_act_code_2, inst_offcl_trnsc_date_2, inst_deg_earned_2,
inst_name_3, inst_act_code_3, inst_offcl_trnsc_date_3, inst_deg_earned_3,
inst_name_4, inst_act_code_4, inst_offcl_trnsc_date_4, inst_deg_earned_4,
inst_name_5, inst_act_code_5, inst_offcl_trnsc_date_5, inst_deg_earned_5,
inst_name_6, inst_act_code_6, inst_offcl_trnsc_date_6, inst_deg_earned_6,
inst_name_7, inst_act_code_7, inst_offcl_trnsc_date_7, inst_deg_earned_7,
inst_name_8, inst_act_code_8, inst_offcl_trnsc_date_8, inst_deg_earned_8,
major_id, major_begin_date,major_stat,major_app_rslt,
cohort_code, effective_yrtr, cohort_end_date,
applicant_yrtr,nbrcredits, major_yrtr,nbrcrds20105
FROM (
MERGE dbo.HOBSONDailyMaster as Target
USING (SELECT tech_id, last_name, first_name, middle_name, name_suff,app_begin_date,
FT_PT, adm_stat, app_type_code, military_serv_flag, ethnic_code,
death_date, Personalemail, Studentemail, immun_code, phone, street1, street2, city,
state_of_resident, zip, MNCounty, cell_phone,
hs_code,hs_grad_date, o_hs_transcript_Rcvd,
ACC_Reading, ACC_Sentence, ACC_Arithmetic, ACC_Elem_Algebra, ACC_Coll_Math,
Waiver_Math, Waiver_Eng, Waiver_Read, Waiver_ASLS, Waiver_OESL,
inst_name_1, inst_act_code_1, inst_offcl_trnsc_date_1, inst_deg_earned_1,
inst_name_2, inst_act_code_2, inst_offcl_trnsc_date_2, inst_deg_earned_2,
inst_name_3, inst_act_code_3, inst_offcl_trnsc_date_3, inst_deg_earned_3,
inst_name_4, inst_act_code_4, inst_offcl_trnsc_date_4, inst_deg_earned_4,
inst_name_5, inst_act_code_5, inst_offcl_trnsc_date_5, inst_deg_earned_5,
inst_name_6, inst_act_code_6, inst_offcl_trnsc_date_6, inst_deg_earned_6,
inst_name_7, inst_act_code_7, inst_offcl_trnsc_date_7, inst_deg_earned_7,
inst_name_8, inst_act_code_8, inst_offcl_trnsc_date_8, inst_deg_earned_8,
major_id, major_begin_date,major_stat,major_app_rslt,
cohort_code, effective_yrtr, cohort_end_date,
applicant_yrtr,nbrcredits, major_yrtr,nbrcrds20105, @CurrentTime AS [Last_Update]
FROM dbo.initiallayout_edit) AS Source
ON (Target.Tech_id= Source.Tech_id)
WHEN MATCHED AND
Target.death_date <> Source.death_date OR
Target.Personalemail <> Source.Personalemail OR
Target.Studentemail <> Source.Studentemail OR
Target.immun_code <> Source.immun_code OR
Target.phone <> Source.phone OR
Target.street1 <> Source.street1 OR
Target.street2 <> Source.street2 OR
Target.city <> Source.city OR
Target.state_of_resident <>Source.state_of_resident OR
Target.zip <> Source.zip OR
Target.MNCounty <> Source.MNCounty OR
Target.cell_phone <> Source.cell_phone
THEN
UPDATE SET
Target.last_name=Source.last_name ,
Target.first_name = Source.first_name ,
Target.middle_name = Source.middle_name ,
Target.name_suff = Source.name_suff ,
Target.app_begin_date = Source.app_begin_date ,
Target.FT_PT = Source.FT_PT ,
Target.adm_stat = Source.adm_stat ,
Target.app_type_code = Source.app_type_code ,
Target.military_serv_flag = Source.military_serv_flag ,
Target.ethnic_code = Source.ethnic_code ,
Target.death_date = Source.death_date ,
Target.Personalemail = Source.Personalemail ,
Target.Studentemail = Source.Studentemail ,
Target.immun_code = Source.immun_code ,
Target.phone = Source.phone ,
Target.street1 = Source.street1 ,
Target.street2 = Source.street2 ,
Target.city = Source.city ,
Target.state_of_resident =Source.state_of_resident ,
Target.zip = Source.zip ,
Target.MNCounty = Source.MNCounty ,
Target.cell_phone = Source.cell_phone ,
Target.hs_code = Source.hs_code ,
Target.hs_grad_date = Source.hs_grad_date ,
Target.o_hs_transcript_Rcvd = Source.o_hs_transcript_Rcvd ,
Target.ACC_Reading = Source.ACC_Reading ,
Target.ACC_Sentence = Source.ACC_Sentence ,
Target.ACC_Arithmetic = Source.ACC_Arithmetic ,
Target.ACC_Elem_Algebra = Source.ACC_Elem_Algebra ,
Target.ACC_Coll_Math = Source.ACC_Coll_Math ,
Target.Waiver_Math = Source.Waiver_Math ,
Target.Waiver_Eng = Source.Waiver_Eng ,
Target.Waiver_Read = Source.Waiver_Read ,
Target.Waiver_ASLS = Source.Waiver_ASLS ,
Target.Waiver_OESL = Source.Waiver_OESL ,
Target.inst_name_1 = Source.inst_name_1 ,
Target.inst_act_code_1 = Source.inst_act_code_1 ,
Target.inst_offcl_trnsc_date_1 = Source.inst_offcl_trnsc_date_1 ,
Target.inst_deg_earned_1 = Source.inst_deg_earned_1 ,
Target.inst_name_2 = Source.inst_name_2 ,
Target.inst_act_code_2 = Source.inst_act_code_2 ,
Target.inst_offcl_trnsc_date_2 = Source.inst_offcl_trnsc_date_2 ,
Target.inst_deg_earned_2 = Source.inst_deg_earned_2 ,
Target.inst_name_3 = Source.inst_name_3 ,
Target.inst_act_code_3 = Source.inst_act_code_3 ,
Target.inst_offcl_trnsc_date_3 = Source.inst_offcl_trnsc_date_3 ,
Target.inst_deg_earned_3 = Source.inst_deg_earned_3 ,
Target.inst_name_4 = Source.inst_name_4 ,
Target.inst_act_code_4 = Source.inst_act_code_4 ,
Target.inst_offcl_trnsc_date_4 = Source.inst_offcl_trnsc_date_4 ,
Target.inst_deg_earned_4 = Source.inst_deg_earned_4 ,
Target.inst_name_5 = Source.inst_name_5 ,
Target.inst_act_code_5 = Source.inst_act_code_5 ,
Target.inst_offcl_trnsc_date_5 = Source.inst_offcl_trnsc_date_5 ,
Target.inst_deg_earned_5 = Source.inst_deg_earned_5 ,
Target.inst_name_6 = Source.inst_name_6 ,
Target.inst_act_code_6 = Source.inst_act_code_6 ,
Target.inst_offcl_trnsc_date_6 = Source.inst_offcl_trnsc_date_6 ,
Target.inst_deg_earned_6 = Source.inst_deg_earned_6 ,
Target.inst_name_7 = Source.inst_name_7 ,
Target.inst_act_code_7 = Source.inst_act_code_7 ,
Target.inst_offcl_trnsc_date_7 = Source.inst_offcl_trnsc_date_7 ,
Target.inst_deg_earned_7 = Source.inst_deg_earned_7 ,
Target.inst_name_8 = Source.inst_name_8 ,
Target.inst_act_code_8 = Source.inst_act_code_8 ,
Target.inst_offcl_trnsc_date_8 = Source.inst_offcl_trnsc_date_8 ,
Target.inst_deg_earned_8 = Source.inst_deg_earned_8 ,
Target.major_id = Source.major_id ,
Target.major_begin_date = Source.major_begin_date ,
Target.major_stat = Source.major_stat ,
Target.major_app_rslt = Source.major_app_rslt ,
Target.cohort_code = Source.cohort_code ,
Target.effective_yrtr = Source.effective_yrtr ,
Target.cohort_end_date = Source.cohort_end_date ,
Target.applicant_yrtr = Source.applicant_yrtr ,
Target.nbrcredits = Source.nbrcredits ,
Target.major_yrtr = Source.major_yrtr ,
Target.nbrcrds20105 = Source.nbrcrds20105,
Target.Last_update = Source.last_update
WHEN NOT MATCHED BY Target THEN
INSERT (tech_id, last_name, first_name, middle_name, name_suff, app_begin_date,
FT_PT, adm_stat, app_type_code, military_serv_flag, ethnic_code,
death_date, Personalemail, Studentemail, immun_code, phone, street1, street2, city,
state_of_resident, zip, MNCounty, cell_phone,
hs_code,hs_grad_date, o_hs_transcript_Rcvd,
ACC_Reading, ACC_Sentence, ACC_Arithmetic, ACC_Elem_Algebra, ACC_Coll_Math,
Waiver_Math, Waiver_Eng, Waiver_Read, Waiver_ASLS, Waiver_OESL,
inst_name_1, inst_act_code_1, inst_offcl_trnsc_date_1, inst_deg_earned_1,
inst_name_2, inst_act_code_2, inst_offcl_trnsc_date_2, inst_deg_earned_2,
inst_name_3, inst_act_code_3, inst_offcl_trnsc_date_3, inst_deg_earned_3,
inst_name_4, inst_act_code_4, inst_offcl_trnsc_date_4, inst_deg_earned_4,
inst_name_5, inst_act_code_5, inst_offcl_trnsc_date_5, inst_deg_earned_5,
inst_name_6, inst_act_code_6, inst_offcl_trnsc_date_6, inst_deg_earned_6,
inst_name_7, inst_act_code_7, inst_offcl_trnsc_date_7, inst_deg_earned_7,
inst_name_8, inst_act_code_8, inst_offcl_trnsc_date_8, inst_deg_earned_8,
major_id, major_begin_date,major_stat,major_app_rslt,
cohort_code, effective_yrtr, cohort_end_date,
applicant_yrtr,nbrcredits, major_yrtr,nbrcrds20105, [Last_Update] )
VALUES (Source.tech_id, Source.last_name, Source.first_name, Source.middle_name, Source.name_suff, Source.app_begin_date,
Source.FT_PT, Source.adm_stat, Source.app_type_code, Source.military_serv_flag, Source.ethnic_code,
Source.death_date, Source.Personalemail, Source.Studentemail, Source.immun_code, Source.phone, Source.street1, Source.street2, Source.city,
Source.state_of_resident, Source.zip, MNCounty, Source.cell_phone,
Source.hs_code,Source.hs_grad_date, Source.o_hs_transcript_Rcvd,
Source.ACC_Reading, Source.ACC_Sentence, Source.ACC_Arithmetic, Source.ACC_Elem_Algebra, Source.ACC_Coll_Math,
Source.Waiver_Math, Source.Waiver_Eng, Source.Waiver_Read, Source.Waiver_ASLS,Source.Waiver_OESL,
Source.inst_name_1, Source.inst_act_code_1, Source.inst_offcl_trnsc_date_1, Source.inst_deg_earned_1,
Source.inst_name_2, Source.inst_act_code_2, Source.inst_offcl_trnsc_date_2, Source.inst_deg_earned_2,
Source.inst_name_3, Source.inst_act_code_3, Source.inst_offcl_trnsc_date_3, Source.inst_deg_earned_3,
Source.inst_name_4, Source.inst_act_code_4, Source.inst_offcl_trnsc_date_4, Source.inst_deg_earned_4,
Source.inst_name_5, Source.inst_act_code_5, Source.inst_offcl_trnsc_date_5, Source.inst_deg_earned_5,
Source.inst_name_6, Source.inst_act_code_6, Source.inst_offcl_trnsc_date_6, Source.inst_deg_earned_6,
Source.inst_name_7, Source.inst_act_code_7, Source.inst_offcl_trnsc_date_7, Source.inst_deg_earned_7,
Source.inst_name_8, Source.inst_act_code_8, Source.inst_offcl_trnsc_date_8, Source.inst_deg_earned_8,
Source.major_id, Source.major_begin_date,Source.major_stat,Source.major_app_rslt,
Source.cohort_code, Source.effective_yrtr, Source.cohort_end_date,
Source.applicant_yrtr,Source.nbrcredits, Source.major_yrtr,Source.nbrcrds20105, @CurrentTime )
OUTPUT $action,Inserted.[tech_id]
,Inserted.Inserted.[last_name],Inserted.Inserted.[first_name],Inserted.Inserted.[middle_name],Inserted.Inserted.[name_suff],Inserted.Inserted.[app_begin_date],Inserted.Inserted.[FT_PT],Inserted.Inserted.[adm_stat],Inserted.Inserted.[app_type_code],Inserted.Inserted.[military_serv_flag],Inserted.Inserted.[ethnic_code],Inserted.Inserted.[death_date],Inserted.Inserted.[Personalemail]
,Inserted.Inserted.[Studentemail],Inserted.Inserted.[Immun_Code],Inserted.Inserted.[phone],Inserted.Inserted.[street1],Inserted.Inserted.[street2],Inserted.Inserted.[city],Inserted.Inserted.[state_of_resident],Inserted.Inserted.[zip],Inserted.Inserted.[MNCounty],Inserted.Inserted.[cell_phone],Inserted.[hs_code],Inserted.[hs_name] ,Inserted.[hs_grad_date],Inserted.[o_hs_transcript_Rcvd]
,Inserted.[ACC_Reading] ,Inserted.[ACC_Sentence] ,Inserted.[ACC_Arithmetic],Inserted.[ACC_Elem_Algebra],Inserted.[ACC_Coll_Math],Inserted.[Waiver_Math],Inserted.[Waiver_Eng],Inserted.[Waiver_Read]
,Inserted.[Waiver_ASLS] ,Inserted.[Waiver_OESL],Inserted.[inst_name_1],Inserted.[inst_act_code_1],Inserted.[inst_offcl_trnsc_date_1],Inserted.[inst_deg_earned_1],Inserted.[inst_name_2]
,Inserted.[inst_act_code_2],Inserted.[inst_offcl_trnsc_date_2],Inserted.[inst_deg_earned_2],Inserted.[inst_name_3],Inserted.[inst_act_code_3],Inserted.[inst_offcl_trnsc_date_3],Inserted.[inst_deg_earned_3],Inserted.[inst_name_4]
,Inserted.[inst_act_code_4],Inserted.[inst_offcl_trnsc_date_4],Inserted.[inst_deg_earned_4],Inserted.[inst_name_5],Inserted.[inst_act_code_5],Inserted.[inst_offcl_trnsc_date_5]
,Inserted.[inst_deg_earned_5],Inserted.[inst_name_6],Inserted.[inst_act_code_6],Inserted.[inst_offcl_trnsc_date_6],Inserted.[inst_deg_earned_6],Inserted.[inst_name_7],Inserted.[inst_act_code_7]
,Inserted.[inst_offcl_trnsc_date_7],Inserted.[inst_deg_earned_7],Inserted.[inst_name_8],Inserted.[inst_act_code_8],Inserted.[inst_offcl_trnsc_date_8],Inserted.[inst_deg_earned_8]
,Inserted.[major_id],Inserted.[major_begin_date],Inserted.[major_stat],Inserted.[major_app_rslt],Inserted.[cohort_code],Inserted.[effective_yrtr],Inserted.[cohort_end_date],Inserted.[applicant_yrtr],Inserted.[nbrcredits],Inserted.[major_yrtr]
,Inserted.[nbrcrds20105])
AS CHANGES (ACTION,tech_id, last_name, first_name, middle_name, name_suff,app_begin_date,
FT_PT, adm_stat, app_type_code, military_serv_flag, ethnic_code,
death_date, Personalemail, Studentemail, immun_code, phone, street1, street2, city,
state_of_resident, zip, MNCounty, cell_phone,
hs_code,hs_grad_date, o_hs_transcript_Rcvd,
ACC_Reading, ACC_Sentence, ACC_Arithmetic, ACC_Elem_Algebra, ACC_Coll_Math,
Waiver_Math, Waiver_Eng, Waiver_Read, Waiver_ASLS, Waiver_OESL,
inst_name_1, inst_act_code_1, inst_offcl_trnsc_date_1, inst_deg_earned_1,
inst_name_2, inst_act_code_2, inst_offcl_trnsc_date_2, inst_deg_earned_2,
inst_name_3, inst_act_code_3, inst_offcl_trnsc_date_3, inst_deg_earned_3,
inst_name_4, inst_act_code_4, inst_offcl_trnsc_date_4, inst_deg_earned_4,
inst_name_5, inst_act_code_5, inst_offcl_trnsc_date_5, inst_deg_earned_5,
inst_name_6, inst_act_code_6, inst_offcl_trnsc_date_6, inst_deg_earned_6,
inst_name_7, inst_act_code_7, inst_offcl_trnsc_date_7, inst_deg_earned_7,
inst_name_8, inst_act_code_8, inst_offcl_trnsc_date_8, inst_deg_earned_8,
major_id, major_begin_date,major_stat,major_app_rslt,
cohort_code, effective_yrtr, cohort_end_date,
applicant_yrtr,nbrcredits, major_yrtr,nbrcrds20105) WHERE ACTION='UPDATE'
;
END
June 15, 2012 at 9:42 am
Going out on a limb here, but I'm pretty sure you can't use a MERGE statement in a derived table.
What exactly are you trying to accomplish here?
June 15, 2012 at 11:01 am
I want to insert the output from merge statement to another table.
June 15, 2012 at 11:04 am
when I run this example below it work. but when I apply my case it didn't work
so I don't know when is wrong
USE AdventureWorks2008R2;
GO
CREATE TABLE Production.UpdatedInventory
(ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701' AND '20030731'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON pi.ProductID = src.ProductID
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply