Inserting same identity value into two linked tables

Hi,
I have two tables which has 2 linked fields, as David Portas posted in
'Field value determines whether there is extra info that should be held
about that record':
CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
different types you have */), UNIQUE (event_no, event_type) /* ...
other columns for events */) ;
CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
DELETE CASCADE /* ... other columns for event type 1 */) ;
I've set the event_no identity property in table 'events' to true. I have a
stored procedure to insert data to both tables at once (using 2 INSERT INTO
statements coming one after another).
The problem is that currently I'm using ADO to generate the new ID (By
selecting MAX of the event_no, adding 1 to the MAX value, and using the
result as the new event_no) which is sent as a parameter to the stored
procedure, which uses it in the 2 INSERT INTO statements.
This method is working fine by now, but could potentially cause problems.
How can generate a new value for that event_no identity column, then use it
in both the INSERT INTO statements?
I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've also
read that there are better options, but these are not relevant since I'm
using SQL Server 7.
Kind Regards,
Amir.Amir wrote:
> Hi,
> I have two tables which has 2 linked fields, as David Portas posted in
> 'Field value determines whether there is extra info that should be held
> about that record':
> CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
> INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
> different types you have */), UNIQUE (event_no, event_type) /* ...
> other columns for events */) ;
> CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
> event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
> KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
> DELETE CASCADE /* ... other columns for event type 1 */) ;
> I've set the event_no identity property in table 'events' to true. I have
a
> stored procedure to insert data to both tables at once (using 2 INSERT INT
O
> statements coming one after another).
> The problem is that currently I'm using ADO to generate the new ID (By
> selecting MAX of the event_no, adding 1 to the MAX value, and using the
> result as the new event_no) which is sent as a parameter to the stored
> procedure, which uses it in the 2 INSERT INTO statements.
> This method is working fine by now, but could potentially cause problems.
> How can generate a new value for that event_no identity column, then use i
t
> in both the INSERT INTO statements?
> I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've als
o
> read that there are better options, but these are not relevant since I'm
> using SQL Server 7.
> Kind Regards,
> Amir.
Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
for example:
CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
INSERT INTO events (event_name, event_type)
VALUES ('foo',1);
SELECT @.@.IDENTITY AS last_identity_value ;
David Portas
SQL Server MVP
--|||Thanks, David!
Kind Regards,
Amir.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137159522.649050.309090@.g47g2000cwa.googlegroups.com...
> Amir wrote:
> Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
> for example:
> CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
> event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
> CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
> INSERT INTO events (event_name, event_type)
> VALUES ('foo',1);
> SELECT @.@.IDENTITY AS last_identity_value ;
> --
> David Portas
> SQL Server MVP
> --
>

Inserting record in DB2 from Sql Server 2000

I am trying to insert a record in DB2 Database which is a Linked Server in SQl SERVER 2000.

I have tried fetching the value from DB2 Database using Openquery which is working fine, but when i try insertion of record it gives an error.

Follwoing is the Tsql I am using for insertion

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS where 1=0') values(9999999)

When i execute this Tsql i get following Error message

Server: Msg 7399, Level 16, State 1, Line 26
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].

What could be the mistake I am making, Please help me out.

Thanks in Advance

Pranjal

You are doing an insert into a table and specifying a filter - it could be complaining about that

Try

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS') values(9999999)

also try

insert into openquery(DB2_DB2T, 'Select * from $ZUDBA01.TPT200_VOLS') values(all the values for the row)

|||

Hi,

I tried the query as you suggested by still I am encountering the same error. Please advice. Thanks