Insert into select from - View

Question

Insert into select from - View

Minnu

SSCertifiable

Points: 6451
More actions
November 19, 2015 at 7:48 am

#302431

CREATE VIEW [dbo].[test_view]
AS
INSERT INTO s_info
(
s_num, s_name
)
SELECT DISTINCT p_id, p_name
FROM unify
ERROR : Incorrect syntax near the keyword 'INSERT'.
Please help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Minnu (11/19/2015)
CREATE VIEW [dbo].[test_view]
AS
INSERT INTO s_info
(
s_num, s_name
)
SELECT DISTINCT p_id, p_name
FROM unify
ERROR : Incorrect syntax near the keyword 'INSERT'.
Please help

You can't do anything in a view except for a single SELECT (and all its capabilities).

What are you trying to do?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Minnu (11/19/2015)
CREATE VIEW [dbo].[test_view]
AS
SELECT DISTINCT p_id, p_name
FROM unify
ERROR : Incorrect syntax near the keyword 'INSERT'.
Please help

You cant use an the INSERT in a view.

You could do

CREATE VIEW [dbo].[test_view]

AS

SELECT DISTINCT p_id, p_name

FROM unify

GO

INSERT INTO s_info

(

s_num, s_name

)

SELECT p_id, p_name

FROM [dbo].[Test_view]

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Minnu SSCertifiable Points: 6451 More actions · Answer 3

Thanks its working good.

also wants to delete from table before insert.

is it possible in this view...

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Minnu (11/19/2015)
Thanks its working good.
also wants to delete from table before insert.
is it possible in this view...

The insert is not part of the view, you might want to use a stored procedure.

CREATE PROCEDURE [dbo].[test_procedure]

AS

DELETE FROM s_info;

--WHERE ?

INSERT INTO s_info

(

s_num,

s_name

)

SELECT DISTINCT

p_id,

p_name

FROM unify;

GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

@Minnu,

I don't mean this as a slam. It's a recommendation. This is some pretty basic stuff that you're talking about and, if your job requires you to work with T-SQL, I strongly recommend that you get some training. Ask the folks at work. "The answer is always 'No' unless you ask".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)