SQL Query - View

Question

Post reply

SQL Query - View

balu.arunkumar

Ten Centuries

Points: 1072
More actions
May 31, 2016 at 9:58 am

#326704

Hi,

I have a table. Its containing query to create a view.

Create Table #Temp (Query Varchar(8000)

Insert into #Temp Select 'Create View Vw_Name as'

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160501'

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160502'

.

.

.

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160531'

Total length of the query in a temp table is 25K.

Now i want to create the view which is available in a #temp table.

Thanks in Advance...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

based on your design, i'd guess you have to refresh this view every day, since it looks like you build one table per day.

i would bet that those individual tables might actually come from yet another table that already has all the data for all days/months/years in it, is it possible to find and query that data, or aggregate it into another table?



Create View Vw_Name

as

Select Col1,Col2,....Col15 from Table_20160501 UNION ALL

Select Col1,Col2,....Col15 from Table_20160502 UNION ALL

--...

Select Col1,Col2,....Col15 from Table_20160531

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

balu.arunkumar (5/31/2016)

Hi,

I have a table. Its containing query to create a view.

Create Table #Temp (Query Varchar(8000)

Insert into #Temp Select 'Create View Vw_Name as'

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160501'

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160502'

.

.

.

Insert into #Temp Select 'Select Col1,Col2,....Col15 from Table_20160531'

Total length of the query in a temp table is 25K.

Now i want to create the view which is available in a #temp table.

Thanks in Advance...

You can't use a temp table in a view. It doesn't make sense. What would happen if the temp table doesn't exist?? Why not use a few UNIONS and forget the temp table entirely?

Something like this.



Create View Vw_Name as

Select Col1, Col2, Col15 from Table_20160501 UNION ALL

Select Col1, Col2, Col15 from Table_20160502 UNION ALL

Select Col1, Col2, Col15 from Table_20160531

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 3

Something like this?



DECLARE @SQL varchar(max);

SET @SQL = (SELECT Query + CHAR(10) FROM #Temp FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)');

EXEC( @SQL);

Or this:



DECLARE @SQL varchar(max) = '';

SELECT @SQL += Query + CHAR(10)

FROM #Temp;

EXEC( @SQL);

Remember to include UNION ALL if you don't have them in your table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

balu.arunkumar Ten Centuries Points: 1072 More actions · Answer 4

balu.arunkumar

Ten Centuries

Points: 1072

June 1, 2016 at 7:59 am

#1882467

Thanks lot Luis Cazares... Its working..