March 27, 2013 at 12:34 pm
I am trying to do a query that I am not sure can be written without using a cursor and building dynamically. But if someone can help me figure out how to do it without having to use cursors that would be awesome.
So I have a table called AppSystem.ApplianceTypes. It has a varchar column named ApplianceTypeTableName that contains the name of other tables in the form of schema.tablename. There are 71 rows of tablenames in the AppSystem.ApplianceTypes table. Each of the 71 tables have a column inside them called MFG. What I want to do is do a query that lists the MFG values in all 71 tables in the same query (like a UNION). By the way, the 71 rows in AppSystem.ApplianceTypes will grow in the future as we add new tables.
If someone can figure this out, I will be praising them as a SQL King/Queen. 🙂 If you don't think it can be done without cursors just let me know and I will figure that part out using the cursor.
Thanks,
David
March 27, 2013 at 12:42 pm
There is no chance anybody can help you based on what you have posted so far. It sounds very painful and MFG certainly seems to be fitting for this architecture, but I think it left off the initial O.
Please take a few a minutes and read the link found at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
March 27, 2013 at 12:58 pm
A rough guess based off a very flimsy description of what you are trying to accomplish.
declare @SQLCmd nvarchar(max);
select
@SQLCmd = stuff((select N'union select MFG from ' + at.TableName + char(13) + char(10)
from AppSystem.ApplianceTypes
for xml path(''),TYPE).value('.','nvarchar(max)'),1,6,'');
exec sp_executesql @SQLCmd;
March 27, 2013 at 1:04 pm
my take on it, was to make a view from the data;
you'd need to update the view whenever new rows get added.
SELECT 'SELECT MFG FROM ' + ApplianceTypeTableName + ' UNION '
FROM AppSystem.ApplianceTypes
CREATE VIEW ALL_MANUFACTURERS
AS
[Results of 71 rows above, removing the last UNION.]
Lowell
March 27, 2013 at 1:25 pm
I am really sorry guys that my post was not very clear or Flimsy as someone put it.
I have created a simple script that you can run to create a dummy setup of what I have.
Maybe it will be clearer now. Just uncomment the cleanup code when you are done to get
rid of the tables.
/*
Setup Test tables to explain my situation better
*/
-- Create Dummy Tables
CREATE TABLE dbo.MainTable
(
TableName varchar(50)
)
CREATE TABLE dbo.DataTable1
(
mfg VARCHAR(5)
)
CREATE TABLE dbo.DataTable2
(
mfg VARCHAR(5)
)
CREATE TABLE dbo.DataTable3
(
mfg VARCHAR(5)
)
CREATE TABLE dbo.DataTable4
(
mfg VARCHAR(5)
)
-- Insert Dummy Data
INSERT INTO dbo.DataTable1 (mfg) VALUES ('AAA')
INSERT INTO dbo.DataTable1 (mfg) VALUES ('ABA')
INSERT INTO dbo.DataTable1 (mfg) VALUES ('ACA')
INSERT INTO dbo.DataTable1 (mfg) VALUES ('ADA')
INSERT INTO dbo.DataTable1 (mfg) VALUES ('AEA')
INSERT INTO dbo.DataTable2 (mfg) VALUES ('BAA')
INSERT INTO dbo.DataTable2 (mfg) VALUES ('BBA')
INSERT INTO dbo.DataTable2 (mfg) VALUES ('BCA')
INSERT INTO dbo.DataTable2 (mfg) VALUES ('BDA')
INSERT INTO dbo.DataTable2 (mfg) VALUES ('BEA')
INSERT INTO dbo.DataTable3 (mfg) VALUES ('BAA')
INSERT INTO dbo.DataTable3 (mfg) VALUES ('BBA')
INSERT INTO dbo.DataTable3 (mfg) VALUES ('BCA')
INSERT INTO dbo.DataTable3 (mfg) VALUES ('BDA')
INSERT INTO dbo.DataTable3 (mfg) VALUES ('BEA')
INSERT INTO dbo.DataTable4 (mfg) VALUES ('BAA')
INSERT INTO dbo.DataTable4 (mfg) VALUES ('BBA')
INSERT INTO dbo.DataTable4 (mfg) VALUES ('BCA')
INSERT INTO dbo.DataTable4 (mfg) VALUES ('BDA')
INSERT INTO dbo.DataTable4 (mfg) VALUES ('BEA')
INSERT INTO dbo.MainTable ( TableName ) VALUES ('dbo.DataTable1')
INSERT INTO dbo.MainTable ( TableName ) VALUES ('dbo.DataTable2')
INSERT INTO dbo.MainTable ( TableName ) VALUES ('dbo.DataTable3')
INSERT INTO dbo.MainTable ( TableName ) VALUES ('dbo.DataTable4')
-- INSERT CODE HERE TO query MFG in all 4 tables using table names in dbo.MainTable
-- Please note DataTable(n) are just example names. There is no name pattern in reality
-- Clean up
/*
DROP TABLE dbo.MainTable
DROP TABLE dbo.DataTable1
DROP TABLE dbo.DataTable2
DROP TABLE dbo.DataTable3
DROP TABLE dbo.DataTable4
*/
GO
March 27, 2013 at 1:29 pm
Wow Lynn, your code seems to have worked for me. I need to do a little more checking but it looks very promising so far.
Thank you
David
March 27, 2013 at 1:34 pm
based on your example code, Lynn's post, barely modified for the column name, works perfectly:
declare @SQLCmd nvarchar(max);
select
@SQLCmd = stuff((select N'union select MFG from ' + TableName + char(13) + char(10)
from MainTable
for xml path(''),TYPE).value('.','nvarchar(max)'),1,6,'');
exec sp_executesql @SQLCmd;
mine also works, creating a view.
Lowell
March 27, 2013 at 1:37 pm
Thanks Lowell, I only prefer Lynn's over yours due to not having the maintenance on the view when adding more entries to the main table. But both were excellent solutions. You guys/gals are great! Thank you and you are life savers. I really didnt want to have to go the cursor route.
March 27, 2013 at 2:52 pm
No gals on this thread, yet.
March 29, 2013 at 6:09 pm
select 'select mfg from'+convert(varchar,ApplianceTypeTableName)+'
union'
from
ApplianceTypes
check you query editor to result to text
and execute above.
copy result in another query editor window and execute.
March 29, 2013 at 6:13 pm
umarrizwan (3/29/2013)
select 'select mfg from'+convert(varchar,ApplianceTypeTableName)+'union'
from
ApplianceTypes
check you query editor to result to text
and execute above.
copy result in another query editor window and execute.
Why all the extra work when you can create and execute the code dynamically?
March 29, 2013 at 6:58 pm
This thread can be closed now. Lynn's ingenious solution worked extremely well and the performance is awesome as well.
With his solution I was able to convert a CLR procedure that took 1 min 20 secs to a native sql query that now takes 1-2 seconds.
Thanks again Lynn 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply