Merging Multiple Tables+Flaging with the Table_Nam

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging Multiple Tables+Flaging with the Table_Nam

Author

Topic

sergiu03
Starting Member

3 Posts

Posted - 2011-03-09 : 09:36:59

Hey,

I encountered something that really made my day horrible. I had to create multiple copies of tiny tables and i thought that would be better to just merge them altogether into a single one, but also adding for each record merged a flag with the table name that the record came from. I was thinking of using the table_name column from the user_tables sys table.
E.g.: I have 100+ tables, all of them have a row named let's say customerid (data type is the same for all the tables) and i need to merge all these 100+ tables (which i can easily/successfully select as select table_name from user_tables where table_name like 'GR_%').
Table1:
Customerid
1
2
3
4

Table2:
Customerid
1
4
5

End result:
Table_merged:
Customerid Table_name
1 Table1
2 Table1
3 Table1
4 Table1
1 Table2
4 Table2
5 Table2

As you can see i don't need distinct customer_ids, in fact i need them even if they are duplicated (but came from different tables)

Can someone help?

Thank you so much!!

sergiu03
Starting Member

3 Posts

Posted - 2011-03-09 : 09:38:09

I forgot to specify, i'm using Toad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-09 : 09:41:04

EXEC sp_msforeachtable 'if parsename(''?'',1) NOT LIKE ''GR_%'' return;
INSERT Table_Merged(CustomerID, TableName) SELECT CustomerID, parsename(''?'',1) FROM ?'

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 09:44:14

Are all tables in the same database and all have the *exact* same schema? In that case you can do something like this and work with the result you get:


SELECT 'SELECT ''' + TABLE_NAME + ''', * FROM ' + TABLE_NAME + ' UNION ALL'
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME LIKE '%prefix_%'

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

sergiu03
Starting Member

3 Posts

Posted - 2011-03-09 : 10:06:31

Hi,

Ok, here is what i did:
create table SU_TABLES_COPY_TEMP
(customer_id number(9),
tables_name varchar2(30))

SELECT 'SELECT ''' + TABLE_NAME + ''', * FROM ' + TABLE_NAME + ' UNION ALL'
FROM user_tables
WHERE TABLE_NAME LIKE 'GR_LST%'

Toad Error: ORA-01722: invalid number

To add, I am using Toad 8.6, I am a beginner (as you may already have noticed) and 50 of the tables that starts with 'GR_%' have one column only (customer_id) while the other have the customer_id plus some other fields, which i am not interested on.
And yes, i am running this query on the same database and schema. I already did it manually, one by one (created copies/backups) but this task is re-occurring monthly...so no deadline now, but i would like to do it altogether from now. I hope i offered all the inputs i could to make you understand what i am looking for.

Thank you!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-09 : 11:11:03

If you're using Oracle, you should check the Oracle forum on http://dbforums.com/

SQLTeam is a Microsoft SQL Server website, we don't answer Oracle questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources