SQL Query - View

• balu.arunkumar

  Ten Centuries

  Points: 1132

  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...

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 31, 2016 at 10:13 am

  #1882195

  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: 286590

  More actions

  May 31, 2016 at 10:13 am

  #1882196

  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: 183706

  More actions

  May 31, 2016 at 10:15 am

  #1882199

  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: 1132

  More actions

  June 1, 2016 at 7:59 am

  #1882467

  Thanks lot Luis Cazares... Its working..