updating an Identity value through a view

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

Transact-SQL (2005)

updating an Identity value through a view

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 11:34:10

I have a partitioned view:

create view vwHeader
as
select * from header1
union all
select * from header2
union all
select * from header3.........

All header tables have an identity attribute as the PK, but when each table is created, the identity value begins from the last tables value:

e.g.
header1
1
2
3
4

header2
5
6
7

header3
8
9.........

I need to explicitly insert an identity value through the above view, but get the error:

Msg 4433, Level 16, State 4, Line 12
Cannot INSERT into partitioned view 'CRDM_PointOfSale.dbo.CRDM_Header' because table '[CRDM_PointOfSale].[crdm].[Header_20090113]' has an IDENTITY constraint.

This is as expected, but I wrapped the view with IDENTITY_INSERT, but this doesn't work.

Are they any work arounds other than having to reference the table itself and define IDENTITY_INSERT ON?????

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 11:54:58

why do you have to explicitly insert values? no need of doing this at all just for filling gaps. this will cause problem when you try to retrieve records in order of entry as this explicit inserts will override default order.
Anyways, if you really want to do this, you need to write an instead of trigger on view and update base tables through it

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 12:22:14

The reason being is because each table (or rather set of tables) will contain data for that day (loaded by a batch process throughout the day). The tables (or partition as I like to call it) are switched online (and thus viewable by the client) by dropping and recreating a view to include the new tables.

However, occasionally, we may receive data for the day or so previous. Rather than include this in todays partition, the data has to be inserted into the correct table (based on date).

Each day, the new partitions are created using dynamic sql. To ensure the TransactionID is unique across all tables (the IDENTITY attribute), the last value inserted in the day previous is used to create the table schema:

e.g.
header7
transactionID
1234
1235
1236

Create table statement......
TransactionID INT NOT NULL IDENTITY (1237,1)

Before 'switching' in the partition, all the data that has a different transaction date is removed into staging tables. These are then inserted by another offline process into the correct online tables. The transactionIDs are still in sequence:

Header1_20090101
1, 20090101
2, 20090101
3, 20090101

Header2_20090102
4, 20090102
5, 20090102

During loading process:
Header3_20090103
6, 20090103
7, 20090101
8, 20090103

Before Header3_20090103 is switched into the view, the incorrect data is removed

select * from Header3_20090103 where date <> 20090103

Result:
staging.Header
7, 20090101

This has to be inserted into header1_20090101
Header1_20090101
1, 20090101
2, 20090101
3, 20090101
7, 20090101

Do you see what I'm trying to do?

The other alternative was to add a buffer when creating the TransactionID:

Create table statement......
TransactionID INT NOT NULL IDENTITY ((1237+1000),1)

But I cannot guarantee that there will only be 1000 backdated transactions.

If you have any other ideas, it would be great to hear them, and get an experts perspective??

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 12:25:26

You mentioned writing a trigger.......

Excuse my ignorance, but what exactly would the trigger do?

Would that create the transactionIDs itself?

Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 12:28:10

so each day , you keep oncreating new tables based on date?why cant you use PARTITION concept in sql 2005 instead? this will allow you to organise each group data o diferent file groupbased on date raher than you have to create new table each time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 12:29:50

quote:
Originally posted by Bex

You mentioned writing a trigger.......

Excuse my ignorance, but what exactly would the trigger do?

Would that create the transactionIDs itself?

Thanks

Hearty head pats

trigger would execute the batch of code written inside whenever the reqd. triggering action (insert,update,delete) takes place on the defined table.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 12:31:09

Currently we do use partitioning, but we plan to offer hosting to our clients, and therefore, as a means to cut costs, we are to use the standard version of sql server rather than enterprise. Therefore, partitioning only comes with the enterprise edition.

Rubbish I know!!!!

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 12:31:19

and in this case you can write an instead of insert trigger on view which whenever you try to insert to view fires and inserts the data to reqd. base table directly with explicit id value after setting IDENTITY_INSERT ON

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 12:36:08

I see.....

Thank you very much. I shall try implementing that method.

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 12:37:43

ok...let me know how you got on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources