Error 156: Incorrect syntax near the word 'INSERT'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error 156: Incorrect syntax near the word 'INSERT'

Author

Topic

rfan99
Starting Member

3 Posts

Posted - 2006-09-14 : 13:30:56

I tried to creat a VIEW in SQL 2000, after copy all rows from ICI1 table to PRODUCT view, I like to add "ALL" in the first row of my VIEW. However, I have the following error.

"Error 156: Incorrect syntax near the word 'INSERT' "

CREATE VIEW dbo.PRODUCT
AS
SELECT PRODCT, PRODCTDES
FROM dbo.ICI1
GO
INSERT INTO dbo.PRODUCT (PRODCT, PRODCTDES) VALUES ('ALL','ALL')
DROP VIEW dbo.PRODUCT

Can someone help me?

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-14 : 13:45:57

That works when I try it, but I think you're using the view incorrectly anyway.

No rows ever get copied into a view. A view is just a saved SELECT statement that runs against the rows in the original table, so in your case this would do the exact same thing as what you are trying

INSERT INTO dbo.ICI1 (PRODCT, PRODCTDES) VALUES ('ALL','ALL')

If you are trying to get all the rows from the table then perhaps this view will be what you really need

CREATE VIEW dbo.PRODUCT
AS
SELECT 'ALL' AS PRODCT, 'ALL' AS PRODCTDES
UNION
SELECT PRODCT, PRODCTDES
FROM dbo.ICI1

Kristen
Test

22859 Posts

Posted - 2006-09-14 : 14:22:38

"perhaps this view will be what you really need"

... provided the user doesn't put a WHERE clause on it!

Kristen

rfan99
Starting Member

3 Posts

Posted - 2006-09-15 : 11:59:39

quote:
Originally posted by snSQL

That works when I try it, but I think you're using the view incorrectly anyway.

No rows ever get copied into a view. A view is just a saved SELECT statement that runs against the rows in the original table, so in your case this would do the exact same thing as what you are trying

INSERT INTO dbo.ICI1 (PRODCT, PRODCTDES) VALUES ('ALL','ALL')

If you are trying to get all the rows from the table then perhaps this view will be what you really need

CREATE VIEW dbo.PRODUCT
AS
SELECT 'ALL' AS PRODCT, 'ALL' AS PRODCTDES
UNION
SELECT PRODCT, PRODCTDES
FROM dbo.ICI1

Your suggestion causes system hang!

Thanks

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-09-15 : 14:08:39

But you can't insert with a view.

About hanging,
INSERT INTO dbo.ICI1 (PRODCT, PRODCTDES) VALUES ('ALL','ALL')
that part hang your system?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 14:21:16

If this code causes your system to hang, then you've got other issues:

CREATE VIEW dbo.PRODUCT
AS
SELECT 'ALL' AS PRODCT, 'ALL' AS PRODCTDES
UNION
SELECT PRODCT, PRODCTDES
FROM dbo.ICI1

How many rows are in the ICI1 table?

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 01:41:25

"But you can't insert with a view"

Errmmm ... "Oh yes you can"

Admitedly not if its going to have a "dummy" UNION in it


CREATE TABLE MyTable
(
	MyColumn varchar(10)
)
GO
CREATE VIEW MyView
AS
SELECT MyColumn
FROM MyTable
GO

INSERT INTO MyView VALUES ('FooBar')
GO

SELECT *
FROM MyTable
GO
DROP TABLE MyTable
GO
DROP VIEW MyView
GO

Kristen

anilkdanta
Starting Member

25 Posts

Posted - 2006-09-18 : 05:56:00

Hi,

Kirsten has given a perfect example of inserting through a view.

Hope rfan99 is through with issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources