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 #tempSelect * from(Select col1, col2 from view1union allSelect col1, col2 from view2union allSelect col1, col2 from view3) as t[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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 myprocascreate table #temptable(col1 varchar(50), col2 varchr(50), col(3) varchar(50))insert into #temptable(col1,col2,col3)select col1, col2, col3 from myviewinsert into #temptable(col1,col2,col3)select col1, col2, col3 from myview2insert into #temptable(col1,col2,col3)select col1, col2, col3 from myview3 |
|
|
obezyanka
Starting Member
24 Posts |
Posted - 2007-09-25 : 10:37:44
|
Van, thank you for catching it. None of the previous suggestions worked. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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. |
|
|
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 herehttp://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 INSERTMadhivananFailing to plan is Planning to fail |
|
|
obezyanka
Starting Member
24 Posts |
Posted - 2007-09-25 : 10:53:02
|
Can I execute the stored procedure directly on the SQL Server? |
|
|
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. |
|
|
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... |
|
|
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" |
|
|
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 1String or binary data would be truncated.The statement has been terminated. |
|
|
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" |
|
|
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! |
|
|
obezyanka
Starting Member
24 Posts |
Posted - 2007-09-25 : 13:26:13
|
Changed the size and it worked! |
|
|
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'? |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
obezyanka
Starting Member
24 Posts |
Posted - 2007-09-25 : 13:54:48
|
How can I save the query in the stored procedure? |
|
|
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. |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
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] ASGOdelete from tblTEMPinsert into tblTEMP(Col1, Col2...)select Col1, Col2... from view |
|
|
Next Page
|