combine all tables in a database with a union query
-
Hi,
all user tables in a database of mine have the same structure and i would like to combine then as 1 virtual table so now and then.
I have done that once by writing code in VBA that constructed a union query.
How can this be done in T-SQL?
-
Hi,
it has to be really special database, because I can hardly imagine how all tables can have the same structure... and to be honest it looks like the design of this DB is not optimal. Maybe the data should be stored in one table instead, or the design changed altogether?
Anyway, under described circumstances you can use UNION (or UNION ALL if you don't want to remove duplicates; using only UNION works as SELECT DISTINCT). You can then create a view and select from the view if you need data from all tables.
CREATE VIEW all_tables
AS
SELECT col1, col2, col3 FROM tbl1
UNION ALL
SELECT col1, col2, col3 FROM tbl2
UNION ALL
SELECT col1, col2, col3 FROM tbl3
-
I agree with Vladan... we have a very strange database indeed...
Another solution (more close to VB) is to read the user tables from the system tables and construct a dynamic query.
Declare @TableName varchar(128)
Declare @SQLCommand varchar(5000) -- Be carefull here... if there are too much tables...!
Declare Cursor1 Cursor Static ForWard_Only For
select sysobjects.name
from sysobjects
where sysobjects.type = 'U'
Open Cursor1
set @SQLCommand = ''
Fetch Next From Cursor1 Into @TableName
While @@Fetch_status = 0
Begin
Select @SQLCommand = @SQLCommand + 'Select * from '+@TableName+''
Fetch Next From Cursor1 Into @TableName
if (@@Fetch_status = 0)
select @SQLCommand = @SQLCommand + ' UNION '
End -- While Fetch
--print @SQLCommand
exec( @SQLCommand)
Close Cursor1
Deallocate Cursor1
GO
------------
When you 've got a hammer, everything starts to look like a nail... -
Great, i think that's what i was looking for. Do i have to put that code in a stored procedure? And if so how do i get it's contents from Microsoft Access?
Where does the output of a PRINT statement go.
I tried debugging a trigger once and wrote some PRINT statements in it but i never saw any of them.
-
You could find some relevant info or useful links in this discussion:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=274719
... or search the forums for more discussions about this problem. It appears periodically - as most questions do.
Unfortunately I can't help you neither with Access nor with Visual Basic, as my knowledge there is very limited.
PRINT shows the text on the Messages tab in Query Analyzer; I don't know how to capture it when the code is not run in QA.
-
The code that you read can be ran in the SQL Query analyser...
The print statement is displayed in the output.... but the 'print' i used (as you see) is in... comments now "--", and normaly you will not see anything fansy...(at least... from this print)
Of course you can run this as a stored procedure (but dont forget the END statement)
------------
When you 've got a hammer, everything starts to look like a nail... -
Okey i'm getting there allmost.
In access i've made a pass-through query wich runs fine. I have a sub-form that's based on that query and now i get the message:
You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform
-
In "access"... I suppose you mean Microsoft Access...?
What this has to do with T-SQL script u ask for?
Please, correct me if i am wrong, but i suppose that you should also join an MS Access Forum for this?
------------
When you 've got a hammer, everything starts to look like a nail... -
No not realy, as discribed all of the work was done at the client side and that is indeed Microsoft Access.
I constructed a union query in VBA and it took some time to execute. Now that it's done at the server it's much quicker.
But my forms and reports are still in MsAccess so i was looking for a way to connect
-
You're not going to be able to use a pass-through query for your subform, much as the error message suggests. That's because Access can't see the structure of the query at design-time, which breaks its little brain.
If you have a fixed number of user tables, then I would recommend putting the union in a view on the server side, and linking to it. The view, obviously, won't be dynamic, so you'll have to alter it whenever your tables change.
If the tables are frequently created and dropped, you may have to go with a different solution. Without seeing your application, I couldn't make a specific suggestion.
Other than to say that perhaps you ought to find a way to consolidate all of the user tables into a single table, with a column that indicates the difference that separates the tables currently.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply