SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedure from the view
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

obezyanka
Starting Member

24 Posts

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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/25/2007 :  10:28:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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


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

Van
Constraint Violating Yak Guru

458 Posts

Posted - 09/25/2007 :  10:29:47  Show Profile  Reply with Quote
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




Edited by - Van on 09/25/2007 10:30:57
Go to Top of Page

obezyanka
Starting Member

24 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/25/2007 :  10:40:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

458 Posts

Posted - 09/25/2007 :  10:50:26  Show Profile  Reply with Quote
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.

Edited by - Van on 09/25/2007 10:51:01
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/25/2007 :  10:51:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 09/25/2007 :  10:53:02  Show Profile  Reply with Quote
Can I execute the stored procedure directly on the SQL Server?
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 09/25/2007 :  10:54:05  Show Profile  Reply with Quote
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 - 09/25/2007 :  10:56:07  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 09/25/2007 :  11:06:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/25/2007 :  13:18:28  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 09/25/2007 :  13:20:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/25/2007 :  13:21:41  Show Profile  Reply with Quote
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 - 09/25/2007 :  13:26:13  Show Profile  Reply with Quote
Changed the size and it worked!
Go to Top of Page

obezyanka
Starting Member

24 Posts

Posted - 09/25/2007 :  13:41:34  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 09/25/2007 :  13:51:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/25/2007 :  13:54:48  Show Profile  Reply with Quote
How can I save the query in the stored procedure?
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 09/25/2007 :  13:57:35  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 09/25/2007 :  13:57:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/25/2007 :  14:01:11  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000