| Author |
Topic |
|
ddrake1984
Starting Member
8 Posts |
 Posted - 2012-03-18 : 20:40:31
|
When I run the following:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
I get a list of the tables:
Table_1
Table_2
What I want to do is ‘pipe’ this information into a further search to get the name column from both tables.
Instead of doing
SELECT name FROM Table_1 UNION SELECT name FROM Table_2
The reason I need to get the tables from the INFORMATION_SCHEMA.TABLES is because I want the lookup to be dynamic, as more tables are added to the db I want them searched instead of hard coding in the tables.
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-18 : 21:38:19
|
This only gives me the table names with the columns.
I need the information in those columns
for example.
Table_1 has 2 columns (name) and (description)
Table_2 has 2 columns (name) and (description)
I need to do a lookup from INFORMATION_SCHEMA.TABLES or COLUMNS to find these 2 tables (Table_1 & Table_2), but I need it to use this lookup to output the columns data (name) and (description) so it will look like this.
(name) (description) from table_1
(name) (description) from table_1
(name) (description) from table_2
(name) (description) from table_2 etc
for the purpose of understanding my example data.
(Suzuki) (motorbike) from table_1
(Honda) (motorbike) from table_1
(Subaru) (car) from table_2
(Honda) (car) from table_2
but when I add another table (i.e. table_3). I want it to dynamically search the TABLES/COLUMNS from INFORMATION_SCHEMA
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-19 : 00:12:19
|
OK let me try and explain this.
I have a db called 'test'
I have a table called INFORMATION_SCHEMA.TABLES (this stores dynamically all the tables that are added to my db)
I have 2 tables called 'Table_1' and 'Table_2'
I have 2 columns in both tables called 'name' and 'description'
the data in those colums are:
Table_1, name: Suzuki, Description: motorbike
Table_1, name: Honda, Description: motorbike
Table_2, name: Subaru, Description: car
Table_2, name: Honda, Description: car
simply, I want to search the INFORMATION_SCHEMA.TABLES and get a list of all the tables that are in this table.
then I want to do a search on the tables that are found in the search to output the name and description columns from those tables.
I want to build the query, not the data. |
|
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-19 : 02:15:55
|
ok, so a friend of a friend (Kevin) assisted me with this, just thought it would be good for others if they come asking the same question.
SQL CODE:
use MyDatabaseName
declare tableList cursor for
select Table_name from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'Products_%_Products'
declare @tableName as nvarchar(128)
declare @sql as varchar(max)
declare @first as bit
set @first = 1
set @sql = ''
open tableList
fetch next from tableList into @tableName
while @@fetch_status = 0 begin
if @first != 1 begin
set @sql = @sql + ' union '
end
set @sql = @sql + 'select CustomText01,Manufacturer,ManufacturerPartNumber,VendorPartNumber from ' + @tableName
set @first = 0
fetch next from tableList into @tableName
end
close tableList
deallocate tableList
print @sql
execute(@sql)
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-19 : 19:23:43
|
ok, the reason it is done like this is because...
our company has purchased a 3rd party application called QuoteWerks. we use this tool to store products etc for invoices and purchase orders. this program stores products in tables based on the vendor.
table_1 would be Ingram Micro
table_2 would be Dicker Data, etc.
I needed a script to look through all these tables for products, (A Search Tool) as we might have a HP Switch in both Ingram Micro table and Dicker Data table. however the 3rd party app doesnt search all the tables, and when we add new vendors (tables), because it is a pos and we dont want to have to get in and write code for every time we add a new vendor. we wanted an automatic process. and I still dont see why this is not a good script. it is a solution to my requirement.
if you can come up with a better method, i'd like to see it. however saying 'this is not a good design. why do you have these in seperate tables' or 'yeah for what not to do' without understanding my requirements is pure rude and I know you dont understand because I have had to repeat my requirements in a language a 4yo could understand and clearly both tkizer and x002548 still ask the question why. I mentioned 'pip' because it has been a usefull command in batch and bash for command line, sql unfortunately couldnt do this most simple of tasks.
A friend and I found a solution and you dont have to go and rubbish it because you dont like my requirements. troll on another forum, not one that is meant for Intellectual people. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-19 : 20:25:07
|
| ok. I am all ears. how do I get all the data into the a table? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-19 : 21:39:20
|
the table name is the vendor name. surrounded by Products so 'Products_%_Products'
Products_IngramMicro_Products
Products_DickerData_Products
Products_ExpressData_Products
the columns I need are
CustomText01 - this is an MYOB account number
Manufacturer - HP, DELL etc
ManufacturerPartNumber - this is the number from HP, DELL etc
Vendor - this field is not stored in the table, so I need somehow to grab the name of the table and put it in here (this is the supplier)
VendorPartNumber - this is the supplier ref number
Description - this is the description of the item
*sample data*
from the table Products_IngramMicro_Products
CustomText01: 42100
Manufacturer: HP
ManufacturerPartNumber: 54863
Vendor: null
VendorPartNumber: HP99981
Description: HP Server
but my question is this. when we load up QuoteWerks and 'create a new datasource' (essentially creating another table in the db) how am I supposed to automatically add this to the 'new' table. am I expected to add more tables to the script? and when more products are added to the tables, do I have to re-run the script every few minutes?
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ddrake1984
Starting Member
8 Posts |
Posted - 2012-03-20 : 00:22:07
|
Yes, I am trying to find a solution to a problem (the 3rd party app), it wasnt my design to have multiple tables and then do a search, because I have first hand experience how difficult it is.
but because the 3rd party app doesnt do a search through their own damn tables, I am left with no alternative to come up with a solution. I am currently looking at visual lightswitch, php and anything else to do this lookup in SQL for me.
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Search Multiple Tables