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
 Stored procedure from the view

Author  Topic 

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 10:26:24
How can I create a stored procedure that combines the results from three views, and puts them in a temp table?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-25 : 10:28:47
[code]Create table #temp
(
col1 ...
col2 ...
...
)

Insert into #temp
Select * from
(Select col1, col2 from view1
union all
Select col1, col2 from view2
union all
Select col1, col2 from view3
) as t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 10:29:47
This is about the 3rd post you've made reguarding this.

create procedure myproc
as

create table #temptable(col1 varchar(50), col2 varchr(50), col(3) varchar(50))

insert into #temptable(col1,col2,col3)
select col1, col2, col3 from myview

insert into #temptable(col1,col2,col3)
select col1, col2, col3 from myview2

insert into #temptable(col1,col2,col3)
select col1, col2, col3 from myview3



Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 10:37:44
Van, thank you for catching it. None of the previous suggestions worked.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 10:40:25
Kristen and Dinakar told you same thing long time ago here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89897



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 10:50:26
No problem. Books online is a big help for stuff like this.

Kristen, Dinakar, Peso...there are basic SQL classes for things like the basics of creating Stored Procedures that you guys might want to attend... lol JK You guys have more than impressed me with your T-SQL skills.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 10:51:01
quote:
Originally posted by Peso

Kristen and Dinakar told you same thing long time ago here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89897



E 12°55'05.25"
N 56°04'39.16"



OP migght forget to create temp table before INSERT

Madhivanan

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

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 10:53:02
Can I execute the stored procedure directly on the SQL Server?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 10:54:05
Yea. Just connect with Query Analyzer and type in the SPs name and run it.
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 10:56:07
Thank you!!! I haven't touched SQl Server for years. It's amazing how quickly you can forget basic stuff...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 11:06:17
I know the feeling.
When you haven't ride a bike for years, you forget where to put your feet and hands.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 13:18:28
I got the part for creating the table but can't insert the records. I get this error each time:
Server: Msg 8152, Level 16, State 6, Line 1
String or binary data would be truncated.
The statement has been terminated.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 13:20:28
You are trying to insert data that is longer than 50 characters (or whatever limit you have set on the columns).



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 13:21:41
Interesting...I have the same size columns as in the original table where the data is coming from!
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 13:26:13
Changed the size and it worked!
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 13:41:34
Works great now. However, after I saved the query as a stored procedure and ran it from the Query Analyzer it created the table and then dissapeared from the stored procedure. How can I save the query within the stored procedure? Also, I added 'drop the table' command. How can I add 'if exists'?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 13:51:21
You don't need to drop temp tables or use if exists as they get dropped once the stored procedure completes. Temp tables only exist for the life of the session.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 13:54:48
How can I save the query in the stored procedure?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 13:57:35
What are you wanting to accomplish? Once you create the SP, the insert and everything is saved with it. Is there another query you are talking about? In the end, what is it that you are wanting to happen.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 13:57:49
You'd have to post your code in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 2007-09-25 : 14:01:11
That's the problem that the insert isn't getting saved. here is the code:

CREATE PROCEDURE [dbo].[test1] AS
GO
delete from tblTEMP
insert into tblTEMP(Col1, Col2...)
select Col1, Col2... from view
Go to Top of Page
    Next Page

- Advertisement -