Select from Multi table !!! - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Select from Multi table !!!

Author

Topic

happyboy
Starting Member

21 Posts

Posted - 2010-03-01 : 04:09:59

Hi all,

I have a database in SQL Server 2000. (for some reasons this can not be changed structures.)

Because of large data, the data was save in each table by date.
For example TL010310, TL020310, TL030310, ... (TLddmmyy).

Now I have to select data from these table to process. I have 2 ideas

1) Create a view to select (using UNION ALL)
2) Insert into temp table (I dont use this because of creating data again)

But the problem is when the user want to select data from date range (user input).
*How to wrire a store procedure to create a view.
*If use exec(@sqlstring) just 8000 char (may larger when date range is two year)

Could any please help me?

The view I would like to create is simple

Create View TL
As
Select '02/24/2010' as Ngay,* From TL240210
Union All
Select '02/25/2010' as Ngay,* From TL250210

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 04:40:46

It is a poor design until you are working on history tables for Datawarehouse project

declare @date1 datetime, @date2 datetime, @sql varchar(max)
select @date1='2010-12-07',@date2='2010-12-12',@sql=''

select @sql=@sql+'select * from TL'+replace(convert(varchar(8),dateadd(day,number,@date1),104),'.','') +' union all ' from
master..spt_values where type='p'
and number between 0 and datediff(day,@date1,@date2)
select @sql=substring(@sql,1,len(@sql)-9)
EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-01 : 08:31:33

I think it would be far better to use the information schema instead of just assuming that every single date actully has a table:

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

Then modify the output you get in to a create view statement.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

happyboy
Starting Member

21 Posts

Posted - 2010-03-02 : 02:56:12

thanks for reply

But my database has many tables. (about 3 years -- > 365*3 table)
so it can not save in varchar variable that max 8000 in sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 03:52:12

How many data are there for each table?

Madhivanan

Failing to plan is Planning to fail

happyboy
Starting Member

21 Posts

Posted - 2010-03-02 : 20:17:19

I have about 70000 rows for each table and table is increased day by day.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-03 : 01:48:11

Are you creating table daily?

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-03 : 03:21:58

You should probably reconsider this database design of yours...with 70k per day creating a new table on a daily basis just doesn't make sense. You will have roughly 25 mill rows/year and you would probably be better off having it all in one partitioned table. If partitioning is not available you can keep your existing design but create a new table every month instead. Makes thing a lot more manageable.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

happyboy
Starting Member

21 Posts

Posted - 2010-03-03 : 21:33:54

Thanks to Lumbago and madhivanan.

As I said:

1) I cannot change the structure of this database because my head office has desinged it. I have the administrator right on the database but I cannot change all tables that existed.

2) The table is create everyday. I really want all of the data are created on one table so that easy to select (maybe my bank thinks that the process is slow on a table with lots of records). My size of database is about 3GB a year.

So I decide:

To create a dynamic view for data from one day to other day depend on the date of user inputted. I consider between two solution

a) write a store procedure to create dynamic view -->encounter: cannot write create view in store procedure . if use exec(@sqlsrting) just about 8000 characters

b) create a temp table then insert data from each table by day --> encounter: much time to insert,create data again --> waste

Please give me best choice

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 01:59:05

You can try something like this


declare @date1 datetime, @date2 datetime, @sql varchar(8000),@i int
select @date1='2010-12-07',@date2='2010-12-12',@sql='',@i=0

create table #t(...)
while @i<=datediff(day,@date1,@date2)+1
begin
	select @sql='select * from TL'+replace(convert(varchar(8),dateadd(day,@i,@date1),104),'.','')  
	insert into #t
	EXEC(@sql)
	set @i=@i+1
end

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-04 : 03:01:50

Ok, here's a suggestion using dynamic sql that might work for you...using dynamic sql exposes you to sql injection though so make sure that you take proper care of that:


DECLARE @sql nvarchar(max)
DECLARE @something int

SET @SQL = 'WITH myCTE (Col1, Col2, Col3) AS ('

SELECT @sql = @sql + 'SELECT Col1, Col2, Col3 FROM ' + table_name + ' UNION ALL ' 
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_name LIKE '%TL%0310%'
  
SET @sql = LEFT(@sql, LEN(@sql) - 10)
SET @sql = @sql + ') '

--> Here you create the actual select that you want to get from your "inline view"
SET @sql = @sql + 'SELECT * FROM myCTE WHERE Col1 >= ' + @something

PRINT @SQL

--EXEC sp_executesql @sql

And BOL says that "The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)" so the size of the sql statement shouldn't be a problem in it self.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 04:00:26

The OP is using version 2000 and you can't use varchar(max) datatype

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-04 : 04:24:02

Crap...I'm so stupid. CTE's don't work either

I was actually thinking a had I decent solution going here. A derived table would work somewhat in the same way but the nvarchar limit will hit rather quick...bummer

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 04:37:09

That was why I changed my solution. Instead of concatenation execute for each table and move to temporary table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources