• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

INCORRECT SYNTAX NEAR KEYWORD 'INSERT'

I have an ADP and I am trying to run an Insert query (from a view).  I get the message:
INCORRECT SYNTAX NEAR KEYWORD 'INSERT'

Here is my code:

INSERT INTO tblICInventory
                      ([strProductID], [strVendorNumber], [strManufactureNumber], [memDescription], [memPODescription], [strStatus], [ysnTrackStock], [strManufacture],
                      [strCurrencyID], [strItemClass], [curCost], [curCurrentCost], [curSalesPrice], [strUnitMeasure], [strUPCCode], [strCategory], [strCategory2],
                      [strCostMethod], [dtmDateEntered], [dtmLastModified], [dtmPosted], [dblRetailPrice], [dblSuggestedRetailPrice], [strPricing])
SELECT     [dbo].[tblEPIC].[UPC], [dbo].[tblEPIC].[Catalog Number], [dbo].[tblEPIC].[Catalog Number], [dbo].[tblEPIC].[Description],
                      [dbo].[tblEPIC].[Legacy Description],
                      CASE [dbo].[tblEPIC].[Chg] WHEN 'D' THEN 'Discontinued' WHEN 'U' THEN 'Phase Out' WHEN 'Q' THEN 'Discontinued' ELSE 'Active' END, - 1,
                      [dbo].[tblEPIC].[Manufacturer], 'USD', 'Stock', [dbo].[tblEPIC].[Cost], [dbo].[tblEPIC].[Cost], [dbo].[tblEPIC].[Col_ 3 Price],
                      CASE [dbo].[tblEPIC].[Unit] WHEN 'E' THEN 'Each' WHEN 'C' THEN 'Hundred' WHEN 'M' THEN 'Thousand' ELSE '' END, [dbo].[tblEPIC].[UPC],
                      [dbo].[tblICCategory].[strCategory], [dbo].[tblICCategory2].[strCategory2], 'Average Cost', { fn NOW() }, { fn NOW() }, { fn NOW() },
                      [dbo].[tblEPIC].[Retail Price], [dbo].[tblEPIC].[Col_ 3 Price], 'None'
FROM         [dbo].[tblEPIC] INNER JOIN
                      [dbo].[tblICCategory] INNER JOIN
                      [dbo].[tblICCategory2] ON [dbo].[tblICCategory].[strCategory] = [dbo].[tblICCategory2].[strCategory] ON
                      [dbo].[tblEPIC].[Item Class] = [dbo].[tblICCategory2].[strDescription2]

Can anyone help?
0
codeguy
Asked:
codeguy
  • 4
  • 2
  • 2
1 Solution
 
jdlambert1Commented:
1. What is an ADP?
2. NOW is not a valid function in T-SQL, the T-SQL equivalent is GetDate():
...  'Average Cost', GetDate(), GetDate(), GetDate(), [dbo].[tblEPIC].[Retail Price],  ...
0
 
jdlambert1Commented:
Also, what do you mean you're trying to run this "from a view"?
0
 
codeguyAuthor Commented:
An ADP (Access Data Project) is just an access file that connects to SQL Server.  I can either create queries/views or run a stored procedure.  I changed the functions to GetDate and got the same message.
 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jdlambert1Commented:
Yeah, GetDate fixed what would have been your next problem. The problem isn't the syntax, as far as SQL Server's concerned, so it has to be the context. Exactly how are you trying to execute this?
0
 
paeloCommented:
Views cannot contain the INSERT or UPDATE syntax, only SELECT queries.  You will want to put this within a stored proc, I think.

-Paul.
0
 
codeguyAuthor Commented:
Thanks Paul:

Can you help with the SP syntax???
0
 
jdlambert1Commented:
If executed from Query Analyzer to create a stored procedure in SQL Server:

CREATE PROCEDURE up_InsertICInventory AS

INSERT INTO tblICInventory
  ([strProductID], [strVendorNumber], [strManufactureNumber], [memDescription], [memPODescription], [strStatus], [ysnTrackStock], [strManufacture],
  [strCurrencyID], [strItemClass], [curCost], [curCurrentCost], [curSalesPrice], [strUnitMeasure], [strUPCCode], [strCategory], [strCategory2],
  [strCostMethod], [dtmDateEntered], [dtmLastModified], [dtmPosted], [dblRetailPrice], [dblSuggestedRetailPrice], [strPricing])
SELECT [dbo].[tblEPIC].[UPC], [dbo].[tblEPIC].[Catalog Number], [dbo].[tblEPIC].[Catalog Number], [dbo].[tblEPIC].[Description],
  [dbo].[tblEPIC].[Legacy Description], CASE [dbo].[tblEPIC].[Chg] WHEN 'D' THEN 'Discontinued' WHEN 'U' THEN 'Phase Out' WHEN 'Q' THEN 'Discontinued' ELSE 'Active' END, - 1,
  [dbo].[tblEPIC].[Manufacturer], 'USD', 'Stock', [dbo].[tblEPIC].[Cost], [dbo].[tblEPIC].[Cost],
  [dbo].[tblEPIC].[Col_ 3 Price], CASE [dbo].[tblEPIC].[Unit] WHEN 'E' THEN 'Each' WHEN 'C' THEN 'Hundred' WHEN 'M' THEN 'Thousand' ELSE '' END, [dbo].[tblEPIC].[UPC],
  [dbo].[tblICCategory].[strCategory], [dbo].[tblICCategory2].[strCategory2], 'Average Cost', GetDate(), GetDate(), GetDate(),
  [dbo].[tblEPIC].[Retail Price], [dbo].[tblEPIC].[Col_ 3 Price], 'None'
FROM [dbo].[tblEPIC] INNER JOIN
  [dbo].[tblICCategory] INNER JOIN
  [dbo].[tblICCategory2] ON [dbo].[tblICCategory].[strCategory] = [dbo].[tblICCategory2].[strCategory] ON
  [dbo].[tblEPIC].[Item Class] = [dbo].[tblICCategory2].[strDescription2]
0
 
paeloCommented:
Stored procedures can handle one or more statements (like jdlambert's example above) and can also take parameters like this:

CREATE PROC dbo.procTemp (
 @var1 varchar(20),
 @var2 int,
 @var3 datetime
)
AS
BEGIN

...T-SQL...

END


-Paul.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now