Solved

INCORRECT SYNTAX NEAR KEYWORD 'INSERT'

Posted on 2004-09-20
8
765 Views
Last Modified: 2012-06-27
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
Comment
Question by:codeguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12107550
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12107556
Also, what do you mean you're trying to run this "from a view"?
0
 

Author Comment

by:codeguy
ID: 12107580
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 15

Expert Comment

by:jdlambert1
ID: 12107610
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
 
LVL 9

Accepted Solution

by:
paelo earned 125 total points
ID: 12107611
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
 

Author Comment

by:codeguy
ID: 12107635
Thanks Paul:

Can you help with the SP syntax???
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12107666
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
 
LVL 9

Expert Comment

by:paelo
ID: 12107711
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question