January 10, 2012 at 8:54 am
I need to set up a process to query against multiple tables and then insert the data from these tables in a holding location. The table i am querying against have the same structure/column names. (This needs to be automated without minimal user input)
Every Financial Year a new table with be created for to hold specific data for that year. The only thing that changes each year, is a prefix number at the end of the name. I have used the query to pull back the list of table names:
SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (name LIKE N'Table')
This would bring back the results:
Table_0910
Table_1011
Table_1112
Table_1213
Taking this result set I now want to query against all these table to give me (union) of the results.
i.e.
Select Column1
From Table_0910
Select Column1
From Table_1011
Select Column1
From Table_1112
but i don't want to manually enter the table names each year, i want the TSQL process to recognise the new table from the change in prefix (which it would from the sysobjects list) and then use this list to query from?
What is the best way to take my list of tables from the sysobjects and then use the table names in the results set in my from query, but loop so each table is queried?
I have tried:
DECLARE @Table AS VARCHAR
SET@Table = (SELECT name FROM sysobjects WHERE (xtype = 'U') AND (name LIKE n'Table'))
SELECT * FROM @Table
this didn't work said i needed to declare @Table???
Thanks
January 10, 2012 at 9:04 am
Bear in mind that this will break if the tables have different structures.
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + CHAR(10),'') +
'UNION ALL SELECT * FROM ' + QUOTENAME(name)
FROM sysobjects
WHERE (xtype = 'U') AND (name LIKE N'Table%')
SELECT @sql = STUFF(@SQL,1,10,'')
EXEC(@SQL)
--edit--
corrected typos
January 10, 2012 at 9:12 am
Thank you so much...
i've never seen anything like this before, but it seems to be working a treat. I'm going to mess around with it further now, but thanks for that..
Cheers
January 11, 2012 at 3:07 am
Thanks for the fast reply yesterday in regards to my query.
Now that i have the result values stored in a variable, is there another way that i could put these values from my query into a holding table?
I have tried to do this using a simple insert but it does error out saying variables can not be inserted into tables. I do apologise, as i thought it would allow a simple insert to be used.
Thanks
January 11, 2012 at 3:36 am
BEGIN TRAN
CREATE TABLE #tables (--all columns here--)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + CHAR(10),'') +
'UNION ALL SELECT * FROM ' + QUOTENAME(name)
FROM sysobjects
WHERE (xtype = 'U') AND (name LIKE N'TB_DIM_ITEM')
SELECT @sql = STUFF(@SQL,1,10,'')
INSERT INTO #tables
EXEC(@SQL)
SELECT * FROM #tables
ROLLBACK
Also, you may be better off stating the columns needed in the dynamic SQL as well (so instead of UNION ALL SELECT *, put UNION ALL SELECT yourColumns).
January 11, 2012 at 3:46 am
Thanks for that...
i'll take this away and have a play around with it...i'll let you know how i get on!! :O)
January 11, 2012 at 4:23 am
This works perfectly...thanks for your help. I'm going to try and break the code up and now and understand what's happening at each stage.
Thanks
January 11, 2012 at 5:15 am
No problem
January 13, 2012 at 2:52 am
Don't know if you'll still be checking the notifications for posts on this thread, but I was thinking about ways to make it more efficient and came up with this -
CREATE TABLE #tables (--all columns here--)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = tblsTxt.sqlCode
FROM (SELECT 'UNION ALL SELECT * FROM ' + QUOTENAME(name) + CHAR(10)
FROM sysobjects
WHERE (xtype = 'U') AND (name LIKE N'Table%')
FOR XML PATH(''), TYPE) tbls(sqlCode)
CROSS APPLY (SELECT STUFF(tbls.sqlCode.value('./text()[1]', 'VARCHAR(MAX)'),1,10,'')) tblsTxt(sqlCode)
INSERT INTO #tables
EXEC(@SQL)
SELECT * FROM #tables
As a comparison, on a test DB here are the timing stats: -
New Version
SQL Server Execution Times:
CPU time = 46 ms, elapsed time = 50 ms.
Old Version
SQL Server Execution Times:
CPU time = 23235 ms, elapsed time = 23280 ms.
Think that's a fairly good improvement 😀
--EDIT--
Obviously those times are just for creating the dynamic SQL, not for actually inserting the data in your #tables.
January 13, 2012 at 3:20 am
Okay thanks for the reply...I will check this way out as well. I have been using the last example you posted and that seems to work a treat. Thanks for your input again... :O)
January 13, 2012 at 4:01 am
I have a question for you...just moving on slightly....
I have a table that holds counts based on different criteria..example
001: Number of customers
002: Customers from Wales
The way it would appear in the table is after the process (stored procedure) has ran:
Description FinYear Count
001: Number of Customers 2010/11 1560
001: Number of Customers 2011/12 2000
002: Customer from Wales 2010/11 150
002: Customer from Wales 2011/12 200
Now the problem comes when we run some criteria but the results set is null...example
003: Customers from France....When this occurs no entry is made into the table however i need to see that entry in the table with the range of FinYears that exist. example
Description FinYear Count
001: Number of Customers 2010/11 1560
001: Number of Customers 2011/12 2000
002: Customers from Wales 2010/11 150
002: Customers from Wales 2011/12 200
003: Customers from France 2010/11 0
003: Customers from France 2010/11 0
The Financial Year is determine by a manual data range entered into a view. The view will pull the range of data we're working with so it could change depending on our preference...Within the stored procedure that gives me the number of customers or customers from wales i use a group by to get the Financial Years, but now i am having problems inserting the null results into my table with a Financial Year.
I currently have a lookup table that has all validation descriptions listed, and those that don't appear in the table i can simply insert using a join between the lookup and holding table where it doesn't already exist, however i would need to enter it multiple times for the financial year range...does that make sense?
Thanks
January 13, 2012 at 4:54 am
I think i have sorted my previous question using your original example to work in my favour...going to try and build on it further now!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply