Partition table with a auto identity column

Question

0.00/5 (No votes)

See more:

Hi Guys,

I have a table with some columns and an primarykey column as integer and auto identity (auto increament by 1).
I am trying to partition this table by moving the old data to another table.Thus I have created a view like this:

SQL

CREATE VIEW [dbo].[JoinTraps]
AS
SELECT     *
From DB2.dbo.OldTable
UNION ALL
SELECT     *
FROM    DB1.dbo.NewTable

(I have also define my check CONSTRAINT on my auto identity column.)

If I don't have Auto Identity every things work fine. but when I have it Insert and update fails!!
Is there any way to partition a table which has auto an identity column as primary key?!

(I have tested different check constraint on different columns but no success.)

Thanks.

Posted 7-Jan-11 8:30am

aidin Tajadod

Add a Solution

3 solutions

Solution 2

I reckon that you'd have to set the autoidentity counter to a start value that is one greater than the largest value in the old table. If your insert into the view there would already be a column with that same value in the primary key and that just can't be. When updating it would be the same thing if there were to be two columns with the same value in the primary key column.

If you set the start value for the autoidentity counter to 1 greater than the largest value in oldtable you should be fine.

Hope that helps!

Best Regards,
Manfred

Posted 7-Jan-11 8:51am

Manfred Rudolf Bihy

Comments

aidin Tajadod 10-Jan-11 13:07pm

Thanks Manfred but it does not work! If I specify the identity column in my insert command I will get this error :
Cannot INSERT into partitioned view 'DB1.dbo.v1' because table 'DB2.[dbo].[t1]' has an IDENTITY constraint.
and if I don't do that I get this error:
Partitioned view DB1.dbo.v1' is not updatable because a value was not specified for partitioning column 'c_int'.

Solution 3

Try using INSTEAD OF INSERT trigger on the view, and then in it, write: INSERT INTO (active table name here) SELECT (columns) FROM INSERTED

Posted 24-Sep-13 13:04pm

Member 10295361

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Keith Barrow · Accepted Answer · 2011-01-07T08:45:00

The Identity column on the archive table doesn't make logical sense. The key should continue to be the one from the orignal table, otherwise relationships to other tables etc will go out of whack and audit information might be lost.
The other thing is that splitting the table like this should not be needed. If you are having performance problems, you should look at your database normalisation and index optimisation. By splitting this table into two you will cause normalisation problems: foreign key constraints will be effected, join conditions mucked up, and you will need have three "categories" of SQL over these tables( one for the new, one for the old and one for all).
If you must do what you are saying (e.g. someone is insisting on it) and you have some control over the DB design consider swapping the int primary key over to a GUID each of these is globally unique.