Is there a solution to this?

[Siddharth Parekh]

Lets say I have two tables, table1 and table2 that have a one to one relationship

Table1 has two columns - number, name
Table2 has two columns - number, address

number is the primary key and is numeric format.

If i combine these two using a View having a simple SELECT statement like the following:

SELECT table1.number, table1.name, table2.address
FROM table1 INNER JOIN table2 ON table1.number=table2.number;

This will give the output in the following format:

NUMBER NAME ADDRESS

The resulting recordset can be modified but if new records are added to this, then it gives a write conflict. The same thing in a MDB works great.

Any ideas how to overcome the write conflict?

Thanks,
Sid.

 

[J. M. De Moor]

Siddharth

What is the constraint between the tables? Also, is it possible to have a
name with no address or vice versa? On a row in either table, are the name
or address columns NULLable? You don't spell these out in your question. If
you provide the DDL it would help. The engine probably can't tell what to
do with the VIEW you describe because and insert can introduce anomalies.
(INNER JOINS using a single column can imply a one side on the target table
in a one to many relationship. MDBs let you get away with that by making
dangerous assumptions.)

You don't say what version of SQL Server you are using, but you can use
INSTEAD OF triggers to spell out what to do on an INSERT using a VIEW. See
BOL.

Joe

Lets say I have two tables, table1 and table2 that have a one to one relationship

Table1 has two columns - number, name
Table2 has two columns - number, address

number is the primary key and is numeric format.

If i combine these two using a View having a simple SELECT statement like the following:

SELECT table1.number, table1.name, table2.address
FROM table1 INNER JOIN table2 ON table1.number=table2.number;

This will give the output in the following format:

NUMBER NAME ADDRESS

The resulting recordset can be modified but if new records are added to

this, then it gives a write conflict. The same thing in a MDB works great.

 

[Siddharth Parekh]

Joe,

Am using SQL Server 2000.

It is possible to have a name with no address and vice versa. I haven't tried the triggers in this situation as yet but will definitely try them. If you have an example of it, that would be great.

Siddharth.
Siddharth

What is the constraint between the tables? Also, is it possible to have a
name with no address or vice versa? On a row in either table, are the name
or address columns NULLable? You don't spell these out in your question. If
you provide the DDL it would help. The engine probably can't tell what to
do with the VIEW you describe because and insert can introduce anomalies.
(INNER JOINS using a single column can imply a one side on the target table
in a one to many relationship. MDBs let you get away with that by making
dangerous assumptions.)

You don't say what version of SQL Server you are using, but you can use
INSTEAD OF triggers to spell out what to do on an INSERT using a VIEW. See
BOL.

Joe

Lets say I have two tables, table1 and table2 that have a one to one relationship

Table1 has two columns - number, name
Table2 has two columns - number, address

number is the primary key and is numeric format.

If i combine these two using a View having a simple SELECT statement like the following:

SELECT table1.number, table1.name, table2.address
FROM table1 INNER JOIN table2 ON table1.number=table2.number;

This will give the output in the following format:

NUMBER NAME ADDRESS

The resulting recordset can be modified but if new records are added to

this, then it gives a write conflict. The same thing in a MDB works great.

 

[James Goodman]

An INSTEAD OF trigger might be your solution:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_49kj.asp

 

[J. M. De Moor]

Siddharth

Look at James' MSDN link. It has a pretty good example.

Joe