May 26, 2009 at 9:45 am
Ok, here's my situation. I want to partition out a table into two. The only problem is that the table CAN have duplicate rows. (it's coming from a different source that I do not have control over)
I have my tables setup as follows:
CREATE TABLE [CT_2008] (
[IDCOL] [int] NOT NULL ,
[SSN] [int] NOT NULL ,
[COL_DAT] [char] (10) NOT NULL ,
[TRADE_CODE] [char] (1) NOT NULL ,
CONSTRAINT [PK__CT_2008__47DBAE45] PRIMARY KEY CLUSTERED ([COL_DAT],[IDCOL],[SSN]) ON [PRIMARY] ,
CONSTRAINT [CK_CT_2008] CHECK ([COL_DAT] >= '2008-01-01' and [COL_DAT] < '2009-01-01')
) ON [PRIMARY]
GO
CREATE TABLE [CT_2009] (
[IDCOL] [int] NOT NULL ,
[SSN] [int] NOT NULL ,
[COL_DAT] [char] (10) NOT NULL ,
[TRADE_CODE] [char] (1) NOT NULL ,
CONSTRAINT [PK__CT_2009__4316F928] PRIMARY KEY CLUSTERED ([COL_DAT], [IDCOL], [SSN]) ON [PRIMARY] ,
CONSTRAINT [CK_CT_2009] CHECK ([COL_DAT] >= '2009-01-01')
) ON [PRIMARY]
GO
CREATE VIEW CT
AS
SELECT * FROM CT_2009
UNION ALL
SELECT * FROM CT_2008
This works fine as long as I don't insert a duplicate row into the view. When I try to turn the identity column on, I can't insert into the view.
I have tried to put all the columns in the two selects except for the identity like this:
CREATE VIEW CT2
AS
SELECT SSN, COL_DAT,TRADE_CODE FROM CT_2009
UNION ALL
SELECT SSN, COL_DAT,TRADE_CODE FROM CT_2008
But then I cannot insert into the view.
The problem is that this table is getting too big to manage. We try to manage a 30gb limit on a single database. The reason for this is because a 30gb backup usually compresses enough to fit on a single-layer dvd. This way we can easily/cheaply backup our whole server to DVDs and move offsite.
The single table will approach 30gb within the next year probably. If I can partition this table, then I can span it across databases and join it back together with a view.
Thanks in advance for any responses to this post.
May 26, 2009 at 9:53 am
hmm why dont you try to take out the distinct values from your subquery result, something like
select distinct (x.a) from
( select a from #temp1
Union all
Select a from #temp2)x
Hope this is what you are looking for 🙂
May 26, 2009 at 10:11 am
That's the problem. I have to preserve the data that I'm getting. I can't modify it.
I figured that I could just setup a staging table with the same structure and turn the identity column on on it, then insert the rows from the staging table into the view.
I would have to set the initial identity column to the greatest value in the current table first, then just hope that I don't create any duplicates in the future.
May 26, 2009 at 10:19 am
Looks to me like the IDENTITY column should be a SID. It should have NO RELATION to the data being inserted other than providing a unique ID for each record. This simply means an extra column in the table that even the user doesn't need to know about. You would then place a separate index on the user known values that allows for duplicate values.
May 26, 2009 at 10:23 am
Looks to me like the IDENTITY column should be a SID
What you are talking about is what I am trying to do. What is a SID identity column and how do I set a identity column to be SID?
May 26, 2009 at 10:28 am
Given the follwoing table:
create table dbo.MyTable (
DataCol1 int,
DataCol2 datetime,
DataCol3 varchar(25)
);
The new table would be like this:
create table dbo.MyNewTable (
MyNewtableID int identity(1,1),
DataCol1 int,
DataCol2 datetime,
DataCol3 varchar(25)
);
Does the visual help? You could place the "ID" column at the end of the table so that you don't have to move data between tables.
May 26, 2009 at 10:30 am
Noticed you are using SQL Server 2000. Any chance of upgrading in the near future? Partitioned tables in SQL Server 2005/2008 would greatly simplify your problem, IMHO.
May 26, 2009 at 10:39 am
Upgrade this server to 2005/2008? Uh . . . nope. No upgrades on this server anytime soon.
May 26, 2009 at 10:55 am
When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.
May 26, 2009 at 11:01 am
Lynn Pettis (5/26/2009)
When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.
Um . . . that's what I have in the example above.
May 26, 2009 at 11:14 am
Will Summers (5/26/2009)
Lynn Pettis (5/26/2009)
When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.Um . . . that's what I have in the example above.
You have to pardon me, been a really busy weekend (oldest daughter graduated from high school, graduation party, cleaning up afterward) and could have used a couple of more days off.
May 26, 2009 at 11:29 am
No problem. I appreciate any help.
May 26, 2009 at 11:32 am
You aren't adding records to previous year data, are you?
May 26, 2009 at 11:44 am
I am getting updates to previous years data. Which could be an insert, delete or an update to data I already have.
May 26, 2009 at 11:51 am
Just to make sure, the "IDCOL" which u have on both the tables, is it "Identity" type column (that autoincrements by itself) or just an integer column.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply