Incorrect Syntax Error

[renee35]

I am struggling with the "Incorrect syntax near the keyword 'UNION'" in the below code. Could someone please tell me what I am doing wrong in the below code:

DECLARE @StartDate datetime
SET @StartDate = '2008-1-1'
DECLARE @EndDate datetime
SET @EndDate = '2008-1-31'

Create table #vacationpay_types (paycodeid int, paycodedesc varchar (255), Type varchar (255))
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcsscsa.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcsscsa.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcsscsa.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcsscsa.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types


UNION

insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcssca2.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcssca2.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcssca2.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcssca2.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types

UNION
(
insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_htrnj.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_htrnj.dbo.vw_RptUSAMstr v
INNER JOIN ss_htrnj.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_htrnj.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_htrnj.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)

drop table #vacationpay_types

Thanks a bunch!!


Thanks a bunch!!

-T

 

[jbenson001]

remove the:
drop table #vacationpay_types

lines before the UNION

 

[jbenson001]

actually remove any drop statments before all UNIONs in your code

 

[SQLSister]

Also if you are doing an insert through a union, you only need the insert part of the statment at the top, not in each section of the union.

"NOTHING is more important in a database than integrity." ESquared

 

[renee35]

Okay, I removed that and now I am getting this error:

Incorrect syntax near the keyword 'insert'. What am I doing wrong now?

DECLARE @StartDate datetime
SET @StartDate = '2008-1-1'
DECLARE @EndDate datetime
SET @EndDate = '2008-1-31'

Create table #vacationpay_types (paycodeid int, paycodedesc varchar (255), Type varchar (255))

insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcsscsa.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcsscsa.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcsscsa.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcsscsa.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)

UNION


insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_hcssca2.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)

(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_hcsscsa.dbo.vw_RptUSAMstr v
INNER JOIN ss_hcssca2.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_hcssca2.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_hcssca2.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)

UNION

insert into #vacationpay_types (paycodeid, paycodedesc, Type)
(
select
paycodeid, paycodedesc,
'Type' = Case when s.paycodedesc like '%Vaca%' then 'Vacation'
when s.paycodedesc like '%Holid%' then 'Holiday' END
from ss_htrnj.dbo.syspaycodes s
where (paycodedesc like '%Holi%' or paycodedesc like '%Vaca%')
)
(
SELECT
p.PCDesc,
c.Name,
v.SSNDisp,
v.EmpNameLFM,
v.EffDate,
v.WeekEnd,
y.Type,
Sum(v.PayUnits) AS Units,
Sum(v.PRAmt) AS Pay,
Sum(v.BillAmt) AS Bill

FROM ss_htrnj.dbo.vw_RptUSAMstr v
INNER JOIN ss_htrnj.dbo.custCustomer c ON c.CustID = v.CustID
INNER JOIN ss_htrnj.dbo.sysPayCodes s ON v.PayCodeID = s.PayCodeID
INNER JOIN #vacationpay_types y on y.PayCodeDesc = s.PayCodeDesc and y.paycodeid = s.paycodeid
INNER JOIN ss_htrnj.dbo.SSProfitCenter p ON v.PCID = p.PCID
WHERE v.EffDate Between @startdate And @enddate
GROUP BY p.PCDesc, c.Name, v.SSNDisp, v.EmpNameLFM, v.EffDate, v.WeekEnd, y.Type
)
drop table #vacationpay_types

Thanks a bunch!!

-T

 

[jbenson001]

You are trying to UNION Insert statements, UNION is for select statemts.

select blah....
UNION
Select blah ...

you are doing:

insert into
select blah
UNION
insert into
select blah..

if you thnink about what UNION is doing, what you have doesn't make sense.

I think you want something like

insert into
select blah
union
select blah
union
select blah