Same Records from Different Table

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

Same Records from Different Table

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-09-21 : 10:27:34

Hello Experts,

I am trying to achieve consolidated result but dont know how to:

Situation

I have multiple table with identical name except their serial no at last i.e.

Table_1
Table_2
Table_3
Table_4 etc

Now i want to fetch similar values but from all the tables i.e.

select count(margin) from table_1 where [adjustment]<>1

Now i have 55 table and i have to repeat above step 55 times, is it possible to have counter on table which increment the table name by 1 ?

Please let me know if i am not able to explain it well.. my apologize

waiting for replies

Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:30:39

One option would be to use dynamic SQL in a while loop.

HTH.

ishchopra
Starting Member

24 Posts

Posted - 2011-09-21 : 10:31:32

Hey,

Can you please tell me how to use it ?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 10:45:02

Hello,

Perhaps something like this;

--create temp global containers
create table ##Table_1 (margin int, adj int)
create table ##Table_2 (margin int, adj int)
create table ##Table_3 (margin int, adj int)
create table ##Table_4 (margin int, adj int)
create table ##Table_5 (margin int, adj int)

create table ##Results (tableName varchar(20), marginCt int)

insert ##Table_1 select 1,0 union all select 2,0
insert ##Table_2 select 2,0 union all select 2,0
insert ##Table_3 select 3,0 union all select 2,1
insert ##Table_4 select 4,0 union all select 2,0
insert ##Table_5 select 5,1 union all select 2,1

--declare vars
declare 
	@sql varchar(1000),
	@ct tinyint,
	@maxCt tinyint

--init vars	
SELECT
	@ct = 1,
	@maxCt = 5

--perform loop
WHILE (@ct <= @maxCt)
BEGIN

	SET 	@sql = 'INSERT ##Results (tableName, marginCt) SELECT ''##Table_' + CAST(@ct AS VARCHAR(10)) + ''', COUNT(margin) FROM ##Table_' +  CAST(@ct AS VARCHAR(10)) + ' WHERE adj <> 1'     
	EXEC(@sql)
	SET @ct+=1
END	

--display results
SELECT * 
FROM ##Results

--clean up example containers
drop table ##Table_1, ##Table_2, ##Table_3, ##Table_4, ##Table_5

--clean up results containers
drop table ##Results

ishchopra
Starting Member

24 Posts

Posted - 2011-09-21 : 11:34:04

Hey Thanks for your efforts but i just need to pull information from all the tables as all the tables is filled with the records (doesnt require to insert things)..

do you think still this procedure will work ?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 15:57:19

yvw,

The example was intended to demonstrate dynamic SQL in a while loop as one option. If you wish to use such a technique, feel free to modify it to meet your particular need.

Best wishes and have a nice day.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-09-21 : 23:11:11

Can you change your database to do it properly and have a single table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 00:53:47

i really cant understand the logic behind creating continuos tables like this with data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources