query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx