Query - 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 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-10-24 : 16:37:12

I have 9 partition tables.Everyday a job will load the data in the one table.

Partition1table First day load
Partition2table second day load
Partition3table third day load
Partition4table fourth day load
Partition5table fifth day load
Partition6table sixth day load
Partition7table seventh day load
Partition8table eight day load
Partition9table ninth day load

After ninth day load job in Partition9table..then next day Partition1table data should be truncated and load the new data on day 10 must be loaded in Partition1table.
then next it should delete Partition2table ...so on.

Like FIFO (FIRST IN FIRST OUT) after 9 load

How should i acheive this with scripts.

Thanks for your help

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-24 : 17:39:07

I think personally I would not use this partition table setup, but you could do it with simple if statements. Here is an example illustrating 30 days of operation.


declare @day int,@i int
set @day = 1
set @i = @day
while @day < 30
begin
	if @i= 9 
	begin
		print cast(@day as varchar(20)) + ' partition9'
	end
	else if @i = 8 
	begin
		print cast(@day as varchar(20)) + ' partition8'
	end
	else if @i = 7 
	begin
		print cast(@day as varchar(20)) + ' partition7'
	end
	else if @i  = 6
	begin
		print cast(@day as varchar(20)) + ' partition6'
	end
	else if @i = 5
	begin
		print cast(@day as varchar(20)) + ' partition5'
	end
	else if @i =4
	begin
		print cast(@day as varchar(20)) + ' partition4'
	end
	else if @i = 3
	begin
		print cast(@day as varchar(20)) + ' partition3'
	end
	else if @i = 2 
	begin
		print cast(@day as varchar(20)) + ' partition2'
	end
	else if @i = 1 
	begin
		print cast(@day as varchar(20)) + ' partition1'
	end
	
set @day = @day + 1
set @i = @i + 1
	if @i > 9 
	begin
	set @i = 1
	end
end

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-10-24 : 18:51:11

Everday there is millions of record loaded instead of storing in one table because of performance issue I thoughtof partitions table switch would help.

Can you suggest best possible solutions for my situation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-24 : 18:54:18

Why aren't you trying the actual partitioned tables feature instead of partitioning the table manually like you are?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-10-24 : 19:27:34

Actually i doing with table partition features..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-10-25 : 09:26:11

thanks

How do i acheive with actual partitioned tables features.