Create Views on similar table names in multiple databases

Question

Create Views on similar table names in multiple databases

TheMastermind

SSChasing Mays

Points: 640
More actions
April 16, 2010 at 8:27 am

#223327

HI,
I'll try to put everything as non ambiguous as possible. We have databases that we import each year having same table names. Now I want to combine them together by creating new database with views that select from the tables of same name in multiple databases into a single view. Example:
IF I have databases A,B,C and x is the table in all those databases then I want to select all columns in table x from DB A,B and C and put them into a single view called x_view. Column names may not be same each year.
I want to achieve this dynamically cuz I have got lots of tables in each db and I want to do for all of them. IF someone can direct me to best possible approach to do this using a script or .NET code it will be greatly appreciated
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think as soon as you try to simplify and abstract stuff, it makes it harder to understand. pseudo code sux sometimes...tablex/database a/b...yuck.

so do you have multiple tables(maybe in other databases), all with the same number of columns, in the same order, but the columns might have different names?

and you want to build the view to combine them, is that right?

Mastermind-612092 (4/16/2010)
HI,
I'll try to put everything as non ambiguous as possible. We have databases that we import each year having same table names. Now I want to combine them together by creating new database with views that select from the tables of same name in multiple databases into a single view. Example:
IF I have databases A,B,C and x is the table in all those databases then I want to select all columns in table x from DB A,B and C and put them into a single view called x_view. Column names may not be same each year.
I want to achieve this dynamically cuz I have got lots of tables in each db and I want to do for all of them. IF someone can direct me to best possible approach to do this using a script or .NET code it will be greatly appreciated
Thanks

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheMastermind SSChasing Mays Points: 640 More actions · Answer 2

Sorry! I thought anyone would be able to grasp the ABC psudocode :-). Anyway I'll elaborate a little further on table x. This table can differ in number of fields and their names. So I dont want to create any dependency on the column name or nmber of columns when I create view. It should select all fields from table x in db A then select all fields in x from db B and so on. We cannot assume that table x has same fields in other dbs but we can assume that table x will be called x in other dbs.

Similar to table X we have other tables with same characteristics... I just didn't want to bring in multiple tables here to create confusion.

Thanks

Paul White SSC Guru Points: 150467 More actions · Answer 3

TheMastermind (4/16/2010)
Sorry! I thought anyone would be able to grasp the ABC psudocode :-).

Not everyone can be a 'master mind' - you'll have to bear with us if we seem a little slow on the uptake 😛

Anyway I'll elaborate a little further on table x. This table can differ in number of fields and their names. So I dont want to create any dependency on the column name or nmber of columns when I create view. It should select all fields from table x in db A then select all fields in x from db B and so on. We cannot assume that table x has same fields in other dbs but we can assume that table x will be called x in other dbs.

There is no really easy/sensible way to do this in T-SQL, but given that the requirement is to merge data from several databases, I would probably use an SSIS package.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 4

i do agree with Paul you can use ssis to merge the 3 databases tables.

Alternatively you can use bcp out to export the tables from each server and then import using bcp in the final consolidation. Your job might get difficult if you and large no of RI constraints.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

It is still not really clear what you want.

Assume that in database A, table X has the following columns: c1, c2, c3 and in database B, table X has the following columns: c1, c4

Sample data from A.x:

c1 c2 c3

aa bb cc

dd ee ff

Sample data from B.x

c1 c4

gg kk

aa hh

What should the your view_x return in this situation ?

/SG

TheMastermind SSChasing Mays Points: 640 More actions · Answer 6

Thanks to all who tried to help me here.... I guess the situation was a little tricky that is why it was a little hard to explain. We kind a gave a rethought on our approach and made few assumptions that made our job easier. In the end I ended up doing UNION for all tables in different dbs.

Just to add to Paul's comments.. I think you guys are the real Masterminds so no offence in anyways....

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 7

sorry i came late to the party, but i was wondering if this would be helpful, or along the lines you were looking for. to execute you would just change the print @sqlcmd to exec(@sqlcmd)

declare @myTable as table (

myID int IDENTITY (1,1),

tableName varchar(max))

declare @i int, @x int, @sqlcmd varchar(max), @tablename varchar(max)

set @i=1

insert into @mytable

select name from sys.tables

select * from @mytable

set @x=(select COUNT(*) from @myTable)

while @i < (@x + 1)

Begin

set @tablename = (select tablename from @myTable where myid=@i)

set @sqlcmd = 'Create View as vw_' + @tablename + ' as Select abc.* from A.dbo.'+@tablename+' abc Union Select def.* from B.dbo.'+@tablename+' def'

print @sqlcmd

set @i = @i + 1

end

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

TheMastermind SSChasing Mays Points: 640 More actions · Answer 8

Yes I was looking something similar to this but I wrote small .NET app to achieve this where I have a little more room to manipulate logic.