CODEWITHSUNDEEP
sqlsql-serversql-insert
kieron oates
kieron oates

Reputation: 115

When inserting into one table, insert into others with the same values

I have 3 tables:

users, contact and pay_roll.

All of the tables share the column user_id which is the primary key and is auto incrementing on all of them, when I insert a new user into the table "users", is there a way to add a blank row to both contact and pay_roll to then be updated later?

My SQL query so far:

INSERT INTO dbo.users (fName, lName) VALUES ('Kieron', 'Oates')

Upvotes: 0

Views: 147

Answers (2)

SMor
SMor

Reputation: 2862

You're doing it wrong; your schema is fundamentally flawed. The "child" tables contact and payroll are dependent on the user table and should NOT have an identity column used as the foreign key to user. Remove the identity property from these columns. And since it is apparent that you want to create a 1:1 relationship between user and each child table, then the user column in contact and payroll should be both primary key and foreign key.

And no - you do not add "blank" rows to be filled in later. Why? For one, you cannot really distinguish between a "blank" row and a non-blank row without making assumptions about the content of columns. This approach will only lead to tears and many many long hours of work at a later date.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use output clause of insert and insert into another table as below:

declare @otheruser table (fname varchar(10), lname varchar(10))
declare @user table (fname varchar(10), lname varchar(10))

insert into @user(fname, lname)
output inserted.fname, inserted.lname into @otheruser
values ('Kieron', 'Oates')

select * from @otheruser
select * from @user

For empty rows you can use as below:

create table #contact  (userid int, fname varchar(10), addr varchar(10))
create table #user (userid int identity(1,1), fname varchar(10), lname varchar(10))
create table #pay_roll (userid int, pay money)

insert into #user(fname, lname)
output inserted.userid, null as fname, null as addr into #contact
values ('Kieron', 'Oates')

Upvotes: 2

Related Questions