Verify that a child record in related table does exist

luisdev schreef op 05-05-2011 18:54:
> I have two linked tables in a MS SQL Server 2005 database:
>
> tblUser {
> id(pk),
> username,
> name,
> surname
> }
>
> tblUserInfo {
> fieldid(pk),
> userid(fk),
> data
> }
>
> They are joined via the tblUser.id = tblUserInfo.userid relationship.
>
> Each record in tblUser needs to have a related record in the
> tblUserInfo table for the data to be complete. In other words, each id
> in tblUser needs to have a related value in the tblUserInfo.data
> column.
>
> I'm trying to write a script to verify that the related record in
> tblUserInfo does exist. If it does it will UPDATE the value in the
> tblUserInfo.data column, else it will INSERT a new row into the
> tblUserInfo table. One way to do it is to loop through each record in
> tblUser and see if it has a related record in the tblUserInfo table
> (tblUser.id = tblUserInfo.userid) . If it does then the UPDATE is
> performed, else an INSERT is performed.
>
> Is this the best way to do this? Or is there some better way that
> doesn't require looping through the tblUser table?

I've created this script that you can run in a test database, I've put
in the relevant comment lines throughout the script.

I hope it's clear.

Best,
Henk

-----8<----->8-----
-- create parent and child tables
create table tblUser (id int, username char(20), name char(20), surname
char(10))
go
create table tblUserInfo (infoid int identity(1,1), id int, data char(20))
go
/*
insert some sample data in both tables
the parent has one record that has a matching record in the child table
and also one record that has no matching record in the child table
*/

insert into tbluser values(1,'hvdb', 'henk',
'vdberg'),(2,'gvdb','greet','vdberg')
go

insert into tblUserInfo (id, data) values( 1, 'somedata')

select parent.id, parent.username, parent.name, parent.surname,
child.id, child.data
from tbluser parent
inner join tbluserinfo child
on parent.id = child.id

/* This next INSERT INTO statement inserts a record in the child table
for every parent id that has no corresponding child record. It does this
in one fell swoop, so you won't have to use a loop.

The trick is "LEFT OUTER JOIN ... WHERE child.id IS NULL"

After the insert, you're guaranteed to have a record in the child table
for each parent id

*/
insert into tbluserinfo (id)
SELECT
parent.id
FROM
tblUser AS parent
LEFT OUTER JOIN
tblUserInfo AS child
ON
parent.id = child.id
WHERE
child.id IS NULL

-----8<----->8-----

On May 5, 8:30*pm, Henk van den Berg wrote:
>
> I've created this script that you can run in a test database, I've put
> in the relevant comment lines throughout the script.
>
> I hope it's clear.
>
> Best,
> Henk
>
> -----8<----->8-----
> -- create parent and child tables
> create table tblUser (id int, username char(20), name char(20), surname
> char(10))
> go
> create table tblUserInfo (infoid int identity(1,1), id int, data char(20))
> go
> /*
> insert some sample data in both tables
> the parent has one record that has a matching record in the child table
> and also one record that has no matching record in the child table
> */
>
> insert into tbluser values(1,'hvdb', 'henk',
> 'vdberg'),(2,'gvdb','greet','vdberg')
> go
>
> insert into tblUserInfo (id, data) values( 1, 'somedata')
>
> select parent.id, parent.username, parent.name, parent.surname,
> child.id, child.data
> from tbluser parent
> inner join tbluserinfo child
> on parent.id = child.id
>
> /* This next INSERT INTO statement inserts a record in the child table
> for every parent id that has no corresponding child record. It does this
> in one fell swoop, so you won't have to use a loop.
>
> The trick is "LEFT OUTER JOIN ... WHERE child.id IS NULL"
>
> After the insert, you're guaranteed to have a record in the child table
> for each parent id
>
> */
> insert into tbluserinfo (id)
> SELECT
> * * * * parent.id
> FROM
> * * * * tblUser AS parent
> LEFT OUTER JOIN
> * * * * tblUserInfo AS child
> ON
> * * * * parent.id = child.id
> WHERE
> * * * * child.id IS NULL
>
> -----8<----->8-----


Thanks Henk, that's great.

How do I first check if the tbluserinfo table already has the related
record before doing the insert? I want to UPDATE the related record if
it does exist in tbluserinfo, else do an INSERT if it does not exist.

luisdev (andyzaatwebmaildotco.za) writes:
> I have two linked tables in a MS SQL Server 2005 database:
>
> tblUser {
> id(pk),
> username,
> name,
> surname
> }
>
> tblUserInfo {
> fieldid(pk),
> userid(fk),
> data
> }
>
> They are joined via the tblUser.id = tblUserInfo.userid relationship.
>
> Each record in tblUser needs to have a related record in the
> tblUserInfo table for the data to be complete. In other words, each id
> in tblUser needs to have a related value in the tblUserInfo.data
> column.
>
> I'm trying to write a script to verify that the related record in
> tblUserInfo does exist. If it does it will UPDATE the value in the
> tblUserInfo.data column, else it will INSERT a new row into the
> tblUserInfo table. One way to do it is to loop through each record in
> tblUser and see if it has a related record in the tblUserInfo table
> (tblUser.id = tblUserInfo.userid) . If it does then the UPDATE is
> performed, else an INSERT is performed.

UPDATE tblUserInfo
SET data = 'tblUser was here!'
FROM tblUserInfo UI
JOIN tblUser U ON UI.userid = U.id

INSERT tblUserInfo(userid, data)
SELECT U.id, 'tblUser was here too!'
FROM tblUser U
WHERE NOT EXISTS (SELECT *
FROM tblUserInfo UI
WHERE UI.userid = U.id)


I'm here assuming that fieldid in tblUserInfo is an IDENTITY column, since
I don't know how you want to fill it. Of the same reason I have these
cheesy texts for the data column, since I don't know what you want there.




--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx