| 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.TABLESI get a list of the tables:Table_1Table_2What I want to do is ‘pipe’ this information into a further search to get the name column from both tables.Instead of doingSELECT name FROM Table_1 UNION SELECT name FROM Table_2The 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 columnsfor 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 etcfor 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_2but 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: motorbikeTable_1, name: Honda, Description: motorbikeTable_2, name: Subaru, Description: carTable_2, name: Honda, Description: carsimply, 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 MyDatabaseNamedeclare tableList cursor forselect 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 bitset @first = 1set @sql = ''open tableListfetch next from tableList into @tableNamewhile @@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 @tableNameendclose tableListdeallocate tableListprint @sqlexecute(@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 Microtable_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_ProductsProducts_DickerData_ProductsProducts_ExpressData_Productsthe columns I need are CustomText01 - this is an MYOB account numberManufacturer - HP, DELL etcManufacturerPartNumber - this is the number from HP, DELL etcVendor - 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 numberDescription - this is the description of the item*sample data*from the table Products_IngramMicro_ProductsCustomText01: 42100Manufacturer: HPManufacturerPartNumber: 54863Vendor: nullVendorPartNumber: HP99981Description: HP Serverbut 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