error with TSQL

[abenitez77]

I am getting this error below when running my insert command. I am using sql server 2008

error:
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.

Code:
Insert Into dbo.CMS_NewVnd_Master(
[Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],
[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],
[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,
GroupVendor, GroupType)
(Select [Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],
[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],
[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,
'Blackhawk Network', 'GiftCard'
From (Select * from dbo.tblv_Claims_2006AndGreater As CL2006
Where Not Exists(Select 1 From dbo.CMS_NewVnd_Master AS cnv
Where cnv.Site_Nbr = CL2006.Site_Nbr AND
cnv.Client_Nbr = CL2006.Client_Nbr AND
cnv.VendorCode = CL2006.VendorCode AND
(cl2006.VendorDesc Like 'Blackhawk Net%' OR cl2006.VendorDesc Like 'Blackhawk Mark%' OR
cl2006.VendorDesc Like 'BLACKHAWK MRKTG%')
)
)
)

 

[gmmastros]

You have a couple problems here. First, you are trying to do an Insert/Select. When you do this, you cannot have parenthesis around the select part. Ex:

[tt][blue]
Insert Into Table(Col1, Col2)
Select ColA, ColB
From SomeOtherTable[/blue][/tt]

Also... you are using a derived table to select from, but you did not give this derived table an alias, which is required.

Insert Into dbo.CMS_NewVnd_Master(
[Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],
[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],
[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,
GroupVendor, GroupType)
Select [Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],
[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],
[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,
'Blackhawk Network', 'GiftCard'
From (Select * from dbo.tblv_Claims_2006AndGreater As CL2006
Where Not Exists(Select 1 From dbo.CMS_NewVnd_Master AS cnv
Where cnv.Site_Nbr = CL2006.Site_Nbr AND
cnv.Client_Nbr = CL2006.Client_Nbr AND
cnv.VendorCode = CL2006.VendorCode AND
(cl2006.VendorDesc Like 'Blackhawk Net%' OR cl2006.VendorDesc Like 'Blackhawk Mark%' OR
cl2006.VendorDesc Like 'BLACKHAWK MRKTG%')
)
)
  As Blah

Note that I removed an opening parenthesis before the first select, and I removed a closing parenthesis from the end, and also included an alias (As Blah).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[abenitez77]

I fixed the problem. this is the new code:

Insert Into dbo.CMS_NewVnd_Master(
[Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],
[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],
[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,
GroupVendor, GroupType)
Select CL2006.[Site_NBR], CL2006.[AuditCode], CL2006.[AuditDesc], CL2006.[PRGIndustryDesc],
CL2006.[PRGSubIndustryDesc], CL2006.[CorpClientDesc], CL2006.[AuditStatus], CL2006.[Client_Nbr],
CL2006.[AuditYearDesc], CL2006.[VendorCode], CL2006.[VendorDesc], CL2006.New_Vnd, CL2006.New_Name,
'Blackhawk Network', 'GiftCard'
From dbo.tblv_Claims_2006AndGreater As CL2006
Where Not Exists(Select 1 From dbo.CMS_NewVnd_Master AS cnv
Where cnv.Site_Nbr = CL2006.Site_Nbr AND
cnv.Client_Nbr = CL2006.Client_Nbr AND
cnv.VendorCode = CL2006.VendorCode
) AND
(cl2006.VendorDesc Like 'Blackhawk Net%' OR cl2006.VendorDesc Like 'Blackhawk Mark%' OR
cl2006.VendorDesc Like 'BLACKHAWK MRKTG%')

 

[SQLSister]

Am I being blind? Why do you need a derived table at all, You don't have a join. Wouldn't this do the same thing? And be possibly be faster and simpler to mainitain?

Insert Into dbo.CMS_NewVnd_Master([Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,GroupVendor, GroupType)
Select [Site_NBR], [AuditCode], [AuditDesc], [PRGIndustryDesc],[PRGSubIndustryDesc], [CorpClientDesc], [AuditStatus], [Client_Nbr],[AuditYearDesc], [VendorCode], [VendorDesc], New_Vnd, New_Name,'Blackhawk Network', 'GiftCard' 
from dbo.tblv_Claims_2006AndGreater As CL2006
Where Not Exists(Select 1 From dbo.CMS_NewVnd_Master AS cnv Where cnv.Site_Nbr = CL2006.Site_Nbr AND cnv.Client_Nbr = CL2006.Client_Nbr AND cnv.VendorCode = CL2006.VendorCode AND (cl2006.VendorDesc Like 'Blackhawk Net%' OR cl2006.VendorDesc Like 'Blackhawk Mark%' OR cl2006.VendorDesc Like 'BLACKHAWK MRKTG%'))

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