| Author |
 Topic  |
|
obezyanka
Starting Member
24 Posts |
 Posted - 09/25/2007 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/25/2007 : 10:28:47
|
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" |
 |
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 09/25/2007 : 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
|
Edited by - Van on 09/25/2007 10:30:57 |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 10:37:44
|
| Van, thank you for catching it. None of the previous suggestions worked. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 09/25/2007 : 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. |
Edited by - Van on 09/25/2007 10:51:01 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 09/25/2007 : 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 |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 10:53:02
|
| Can I execute the stored procedure directly on the SQL Server? |
|
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 09/25/2007 : 10:54:05
|
| Yea. Just connect with Query Analyzer and type in the SPs name and run it. |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 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
Sweden
29156 Posts |
Posted - 09/25/2007 : 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 - 09/25/2007 : 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.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/25/2007 : 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 - 09/25/2007 : 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 - 09/25/2007 : 13:26:13
|
| Changed the size and it worked! |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 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
USA
35017 Posts |
Posted - 09/25/2007 : 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/ |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 13:54:48
|
| How can I save the query in the stored procedure? |
|
|
|
Van
Constraint Violating Yak Guru
456 Posts |
Posted - 09/25/2007 : 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
USA
35017 Posts |
Posted - 09/25/2007 : 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/ |
|
|
|
obezyanka
Starting Member
24 Posts |
Posted - 09/25/2007 : 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 |
|
|
|
Topic |
|
Stored procedure from the view
