get data from unknow at desing tables

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

SQL Server 2008 Forums

Transact-SQL (2008)

get data from unknow at desing tables

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-28 : 06:53:14

Hi. I have some table that will be created at run time.
This will tell me what the table names are:


select TempTableName from DBTempSyncScenario

So what i want to do is get some columns from all the created tables.
So let's suppose these tables have customer and providence:

I want to do something like this:


select Customer, province
from
(
select  ts.Customer, ts.province from tempscenario tx,tempscenario2 ts
)t

But i need all the tables data into these 2 columns (so merged ts and tx)
Now firstly i know this is wrong since it will duplicate records so the first question is how to fix this as to display all the record and not duplicates.

The second question is where i draw the line.
I have no idea how would i do it.
Basically i would need something like this:


select Customer, province
from
(
select  dbs.Customer, dbs.province from (select TempTableName from DBTempSyncScenario) as dbs

)t

I am not using temp tables but i have no problem using them.
What i suppose could be done is somehow merger all the tables TempTableName data into a temp table and go from there. So,
something like this:



select Customer,province into #newtable from
( 
select * from tempscenario
union all
select * from tempscenario2
union all
select * from tempscenariocsv
) A

select Customer, province
from #newtable

Looks better i guess but again i have to loop the tables so i can put them in the union clause.

So any clue?
Thanks.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-28 : 09:41:27

Are tempscenario, tempscenario2, and tempscenariocsv always the names?

djj

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-28 : 10:12:40

Hi.
No the table names are selected from another table(select TempTableName from DBTempSyncScenario) that in contrast does not always have the same names

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-28 : 13:20:20

If the table names are not the same then some sort of dynamic SQL is going to be needed as T-SQL does not allow for table name variables.

djj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 15:21:32

Here's a framework. Due to a proxy server at my work, I have to add extra spaces in certain command words in my posts. Please remove those.


d eclare @dsql nvarchar(4000), @id smallint, @rc smallint
set @id = 1

d eclare @temp1 table (Customer varchar(1000), province varchar(1000))
d eclare @DBTempSyncScenario table (TempTableName sysname)

insert into @DBTempSyncScenario values ('table1')
insert into @DBTempSyncScenario values ('table2')
insert into @DBTempSyncScenario values ('table3')

select identity(smallint, 1, 1) as TempTableId, 'select Customer, province from ' + TempTableName as TempTableQuery
into #temp2
from @DBTempSyncScenario

set @rc = @@rowcount

while @id <= @rc
begin
	select @dsql = TempTableQuery
	from #temp2
	where TempTableId = @id

	insert into @temp1
	e xec (@dsql)

	set @id = @id + 1
end

select Customer, province
from @temp1

d rop table @DBTempSyncScenario
d rop table @temp1
d rop table #temp2

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-28 : 17:09:24

Hey, thanks i will have a look at it tomorrow. Looks promising!

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-28 : 18:13:14

Hey.
As a quick check before i go to bed i get
Incorrect syntax near '@DBTempSyncScenario'
it's on drop table @DBTempSyncScenario and on drop table @table1

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-29 : 02:39:29

So tested at work today. This works great! Kudos! I removed the drop @DBtempsyncscenario and @drop @table1.
Am i correct to think that since they are not actual table object in the db, they don't need to be dropped? Thanks.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-29 : 08:46:13

Technically they do not need dropped, but cleaning up is a good habit.

djj

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-29 : 10:02:46

Well, i don't know the technique to drop them since it's giving me the error mentioned above. So i was guessing it's just memory objects at runtime and get dropped or garbage collected automatically after the query execution.
I'm not sure, just guessing here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-29 : 12:43:05

You don't even need the @BDTemp.. object as you have a permanent table with the info. Remember I provided just a framework, basically pseudocode. Revise as needed. Yes the drop will error for that one. I didn't do syntax checks, etc.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-30 : 07:50:45

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources