Auto incrementing numbers

1 Jul 2005 10:39 AM

mark

Hi

I have two tables (header and child) with a relationship between them.
Each time I insert a row into the child table, I want to have one of
the columns auto increment. I can't use an identity column (at least I
don't think I can) because the start and increment values can be
configured by the user. Also, the value resets for each header row.
Finally, the INSERT statements source is another table.

Here's some DDL to set it up:

CREATE TABLE #HeaderTable
(
HeaderReference nvarchar(10) NOT NULL
)
GO

CREATE TABLE #ChildTable
(
HeaderReference nvarchar(10),
Sequence float NOT NULL,
ChildReference nvarchar(10)
)
GO

CREATE TABLE #Source
(
HeaderReference nvarchar(10),
ChildReference nvarchar(10)
)

ALTER TABLE #HeaderTable WITH NOCHECK ADD CONSTRAINT PK_HeaderTable
PRIMARY KEY CLUSTERED (HeaderReference)
GO

ALTER TABLE #ChildTable ADD CONSTRAINT FK_ChildTable_HeaderTable
FOREIGN KEY (HeaderReference) REFERENCES HeaderTable (HeaderReference)
ON DELETE CASCADE ON UPDATE CASCADE
GO

INSERT INTO #HeaderTable (HeaderReference) VALUES ('Header A')
INSERT INTO #HeaderTable (HeaderReference) VALUES ('Header B')
INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
A', 'Child A')
INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
A', 'Child B')
INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
B', 'Child A')
INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
B', 'Child B')

INSERT INTO #ChildTable (HeaderReference, Sequence, ChildReference)
SELECT HeaderReference, '<Need to insert sequence here>',
ChildReference FROM #Source

DROP TABLE #HeaderTable
DROP TABLE #ChildTable
DROP TABLE #Source

(The foreign key constraint won't be setup on temporary tables - I made
them temporary so I didn't mess up your databases!)

The final INSERT into #ChildTable needs to auto increment the sequence
number.

Assuming the increment start and step is 1 and 2, the output I want
from #ChildTable would be:

(SELECT * FROM #ChildTable)

HeaderReference    Sequence    ChildReference
Header A           1           Child A
Header A           3           Child B
Header B           1           Child A
Header B           3           Child B

Any ideas?

Many Thanks

1 Jul 2005 11:13 AM

Rick Sawtell

Show quote

"mark" <markne***@gmail.com> wrote in message
news:1120214390.588313.208690@g14g2000cwa.googlegroups.com...
> Hi
>
>
> Assuming the increment start and step is 1 and 2, the output I want
> from #ChildTable would be:
>
> (SELECT * FROM #ChildTable)
>
> HeaderReference    Sequence    ChildReference
> Header A           1           Child A
> Header A           3           Child B
> Header B           1           Child A
> Header B           3           Child B
>
> Any ideas?
>
> Many Thanks
>

A trigger on the child table would work for this.

You mentioned that the sequence starting and/or increment value would be
user configurable. You will need to store this information in another table
as well.

Rick Sawtell
MCT, MCSD, MCDBA

1 Jul 2005 1:30 PM

JT

If you could perhaps talk the client into removing the requirement that ID
values be user defined, then that would simplify things a lot. Otherwise,
storing last used ID in a seperate table can be a source for referential
integrity and locking problems. Be sure that you are wrapping things
properly in transactions.

Show quote

"mark" <markne***@gmail.com> wrote in message
news:1120214390.588313.208690@g14g2000cwa.googlegroups.com...
> Hi
>
> I have two tables (header and child) with a relationship between them.
> Each time I insert a row into the child table, I want to have one of
> the columns auto increment. I can't use an identity column (at least I
> don't think I can) because the start and increment values can be
> configured by the user. Also, the value resets for each header row.
> Finally, the INSERT statements source is another table.
>
> Here's some DDL to set it up:
>
> CREATE TABLE #HeaderTable
> (
> HeaderReference nvarchar(10) NOT NULL
> )
> GO
>
> CREATE TABLE #ChildTable
> (
> HeaderReference nvarchar(10),
> Sequence float NOT NULL,
> ChildReference nvarchar(10)
> )
> GO
>
> CREATE TABLE #Source
> (
> HeaderReference nvarchar(10),
> ChildReference nvarchar(10)
> )
>
> ALTER TABLE #HeaderTable WITH NOCHECK ADD CONSTRAINT PK_HeaderTable
> PRIMARY KEY CLUSTERED (HeaderReference)
> GO
>
> ALTER TABLE #ChildTable ADD CONSTRAINT FK_ChildTable_HeaderTable
> FOREIGN KEY (HeaderReference) REFERENCES HeaderTable (HeaderReference)
> ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> INSERT INTO #HeaderTable (HeaderReference) VALUES ('Header A')
> INSERT INTO #HeaderTable (HeaderReference) VALUES ('Header B')
> INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
> A', 'Child A')
> INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
> A', 'Child B')
> INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
> B', 'Child A')
> INSERT INTO #Source (HeaderReference, ChildReference) VALUES ('Header
> B', 'Child B')
>
> INSERT INTO #ChildTable (HeaderReference, Sequence, ChildReference)
> SELECT HeaderReference, '<Need to insert sequence here>',
> ChildReference FROM #Source
>
> DROP TABLE #HeaderTable
> DROP TABLE #ChildTable
> DROP TABLE #Source
>
> (The foreign key constraint won't be setup on temporary tables - I made
> them temporary so I didn't mess up your databases!)
>
> The final INSERT into #ChildTable needs to auto increment the sequence
> number.
>
> Assuming the increment start and step is 1 and 2, the output I want
> from #ChildTable would be:
>
> (SELECT * FROM #ChildTable)
>
> HeaderReference    Sequence    ChildReference
> Header A           1           Child A
> Header A           3           Child B
> Header B           1           Child A
> Header B           3           Child B
>
> Any ideas?
>
> Many Thanks
>