StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I use a dynamic table name in the FROM area of a SQL query?
text
Body
copied!<p><strong>Original question:</strong> I'm working with a database that generates a new table each month and appends <code>YYYY_MM</code> to the new table name. I need to write a query that will look at the table for the current month. Date functions aside, I find I'm unable to build table names!</p> <p>Example table name would be <code>DOCUMENTS_2011_10</code>. </p> <p>I tried to use a basic script like this but it fails on syntax issue:</p> <pre><code>SELECT * FROM ('DOCUMENTS_'+'2011_10') </code></pre> <p>Any suggestions?</p> <p><strong>ADDITIONAL INFO:</strong> The tables are being generated by our enterprise portal application (Oracle WebCenter Interaction). It is out of my control to redesign this application to change how it handles its data.</p> <p>I've been asked to extract a report showing key data from these tables which are generated monthly.</p> <p>My approach was going to be to create a SQL Reporting Service subscription that will email the data each month in Excel format.<br> I needed help with this query to plug it into the reporting service so I wouldn't have to dabble with the query each month to change the tablename.</p> <p>Because the reporting service will trigger each month and generate the reports, an archive of data, or any type of ad hoc capability are not needed.</p> <p>Using dynamic SQL I was able to set the table name, unfortunately when I try to add the rest of my query it complains of a character limit issue. Any idea how to get around that?</p> <p>This gets me the correct table:</p> <pre><code>Declare @tblName Varchar(400) Declare @SQL Varchar(500) Set @tblName = 'analyticsdbuser.ASFACT_DOCUMENTVIEWS_' + CONVERT(VARCHAR,DATEPART(yyyy,GETDATE())) + '_' + CONVERT(VARCHAR, RIGHT('0' + RTRIM(MONTH(GETDATE())-1), 2)); SET @SQL = 'SELECT * FROM ' + @Tblname; Exec(@SQL) </code></pre> <p>This is the other query that all needs to get executed but won't fit into the @SQL:</p> <pre><code>SELECT t2.ID, t2.USERID, t3.NAME, t2.DOCUMENTID, t1.NAME AS DOC_NAME, t4.PROPERTYID, t5.NAME AS PROP_NAME, t4.VALUE FROM ASFACT_DOCUMENTVIEWS_2011_10 AS t2 INNER JOIN ASDIM_USERS AS t3 ON t3.USERID = t2.USERID INNER JOIN ASDIM_USERPROPERTYVALUES AS t4 ON t4.USERID = t2.USERID INNER JOIN ASDIM_KDDOCUMENTS AS t1 ON t1.ID = t2.DOCUMENTID INNER JOIN ASDIM_USERPROPERTIES AS t5 ON t4.PROPERTYID = t5.PROPERTYID WHERE (t2.DOCUMENTID IN ('33449', '36241', '36566')) AND (t4.PROPERTYID IN (26, 156, 157, 158, 159, 325, 160, 162)) ORDER BY t2.DOCUMENTID, t3.NAME; </code></pre> <p><strong>PARTITIONING QUESTION</strong> I'm unfamiliar with the partitioning concept that some folks have mentioned and will look into it at this time.</p> <p>In case it matters, each of these monthly tables is only about 20k rows and 1.5mb (we aren't a big company, this software was designed for companies that get millions of rows in each of these monthly tables)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload