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
 General SQL Server Forums
 New to SQL Server Programming
 Help

Author  Topic 

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 08:47:45
I am trying to put the following code in the view section but it tells me that only the "select function" can be used. How do I switch this to other functions?

INSERT INTO Contact (Name, Address, PhoneNumber, DOB, PetsOwned)
VALUES ('David Dog Dogowner', '10 Canine Drive', '(555) 743-8747', '5/7/1968', '4')

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-03-21 : 08:51:54
You can't have insert statement in View or UDF. You have to use Stored Procedure for this.
What is your requirement ?


KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-03-21 : 08:52:01
Why do you want to create view that insert records to a table?
Give more details on what you are trying to do

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-03-21 : 08:53:44
>>You can have insert statement in View or UDF.

Did you mean "You can't have insert statement in View or UDF."?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 09:13:10
To answer both of your questions, my requirement is to insert data into an existing database by using the insert function but Like I said in my first post, I received an eror message.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-03-21 : 09:16:47
You will get the error if you try to put that section of code in the create view statement.

What are you trying to achieve ?


KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 09:18:18
How do i use Stored Procedure for inserting statements in View or UDF?
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 09:20:42
I also have another question. It's about the Update function. For my database, I have to set the # of pets owned to 2 when a person's phone number has the number 6. How would I go about doing that?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-03-21 : 09:32:57
quote:
Originally posted by Denis McElligott

How do i use Stored Procedure for inserting statements in View or UDF?


create procedure your_procedure
    @Name        varchar(50),
    @Address     varchar(50),
    @PhoneNumber varchar(20),
    @DOB         datetime,
    @PetsOwned   int
as
begin
    insert into Contact(Name, Address, PhoneNumber, DOB, PetsOwned)
    select @Name, @Address, @PhoneNumber, @DOB, @PetsOwned
end



KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts
Posted - 2006-03-21 : 09:34:04
quote:

How do i use Stored Procedure for inserting statements in View or UDF?


you can not inserted data into the UDF.. and for inserting data in the view also there are restrictions on that you can read the book online view for the same.

what are you trying to acheive??

do you mean that you want to insert the records using the select statements???

then it will be somthing like these..

Insert <TableName> ( <ColumnName1>,<ColumnName2>...)
Select ColumnName1>,<ColumnName2>... From TableName
??

Please explain your case with some example.. database scripts.. etc.. so that we can help you in the better and faster way ..

Hope this helps.

Sucess Comes to those who Believe in Beauty of their Dream..
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts
Posted - 2006-03-21 : 09:36:14
quote:

I also have another question. It's about the Update function. For my database, I have to set the # of pets owned to 2 when a person's phone number has the number 6. How would I go about doing that?


Somthing like this??

Update <TableName> Set Pets = 2 Where PhoneNumber Like '%6%'



Sucess Comes to those who Believe in Beauty of their Dream..
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 10:26:23
quote:
Originally posted by chiragkhabaria

quote:

How do i use Stored Procedure for inserting statements in View or UDF?


you can not inserted data into the UDF.. and for inserting data in the view also there are restrictions on that you can read the book online view for the same.

what are you trying to acheive??

do you mean that you want to insert the records using the select statements???

then it will be somthing like these..

Insert <TableName> ( <ColumnName1>,<ColumnName2>...)
Select ColumnName1>,<ColumnName2>... From TableName
??

Please explain your case with some example.. database scripts.. etc.. so that we can help you in the better and faster way ..

Hope this helps.

Sucess Comes to those who Believe in Beauty of their Dream..


Here is what the table looks like:

Name Address PhoneNumber DOB PetsOwned
Jane Doe 90 River St. (987) 654-3210 1/1/1900 2
Mike Myers 100 Holbrook Rd. (781) 653-8942 9/7/1970 2
Doug Burnett 25 Vincent St. (203) 723-9851 10/1/1980 2

Here is what I want the table to look like after I use the insert function- INSERT INTO Contact (Name, Address, PhoneNumber, DOB, PetsOwned)
VALUES ('David Dogowner', '10 Canine Drive', '(555) 743-8747', '5/7/1968', '4'):

Name Address PhoneNumber DOB PetsOwned
Jane Doe 90 River St. (987) 654-3210 1/1/1900 2
Mike Myers 100 Holbrook Rd. (781) 653-8942 9/7/1970 2
Doug Burnett 25 Vincent St. (203) 723-9851 10/1/1980 2
David Dogowner 10 Canine Drive (555) 743-8747 1/1/1900 4

When I go to put the code in, I go to "Views", then I right click and select "New View', then I paste the insert code that I made and thats where I am having problems. I'm sure that if I solve this problem, it will answer my question with the update function.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-03-21 : 10:36:34
where did you get the idea that you have to use views??
views are used only to display data from n different tables that are joined in a n number of ways.
they aren't meant for inserting data.

insert statement is run either in stored procedure or by itself.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 10:45:10
quote:
Originally posted by spirit1

where did you get the idea that you have to use views??
views are used only to display data from n different tables that are joined in a n number of ways.
they aren't meant for inserting data.

insert statement is run either in stored procedure or by itself.

Go with the flow & have fun! Else fight the flow


stored procedure or by itself? How do I go about putting the insert statement in stored procedure or by itself?
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 10:45:52
quote:
Originally posted by spirit1

where did you get the idea that you have to use views??
views are used only to display data from n different tables that are joined in a n number of ways.
they aren't meant for inserting data.

insert statement is run either in stored procedure or by itself.

Go with the flow & have fun! Else fight the flow


Do I do the same thing for the update statement?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-03-21 : 10:53:22
yes. it's same for both.
where are you executing these statements?
if in Query Analyzer then just write it in the window and press F5 (run)
khtan showed you how to write a stored procedure.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 11:02:44
I went to stored procedure and put this code in:

CREATE PROCEDURE [Insert]
@Name varchar(50),
@Address varchar(50),
@PhoneNumber varchar(20),
@DOB datetime,
@PetsOwned int
as
begin
insert into Contact (David Dogowner, 10 Canine Drive, 555-743-8747, 5/7/1968, 4)
select @Name, @Address, @PhoneNumber, @DOB, @PetsOwned
end

when I clicked on "Ok", it said Error 170: Line 9: Incorrect Syntax near 'Dogowner'. I don't understand why it's saying that.
Go to Top of Page

Denis McElligott
Starting Member

18 Posts
Posted - 2006-03-21 : 11:04:01
quote:
Originally posted by spirit1

yes. it's same for both.
where are you executing these statements?
if in Query Analyzer then just write it in the window and press F5 (run)
khtan showed you how to write a stored procedure.

Go with the flow & have fun! Else fight the flow


I am executing these statements in Stored Procedure Properties.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-03-21 : 11:06:01
stored procedure proerties????
whoa there!!!

What are you using to access your sql server? Enterprise Manager, Query analyzer, something else?


Go with the flow & have fun! Else fight the flow
Go to Top of Page

jhermiz

3564 Posts
Posted - 2006-03-21 : 11:24:01
Probably a good idea not to rush into this, pick up a good book and read about sql server and the tools associated with it.

Don't get too caught up with trying to finish the project as fast as possible. You want to make sure you do a good job and have a good product, so take your time and do the research.



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-03-21 : 11:25:44
listen to him! he knows

Go with the flow & have fun! Else fight the flow
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site