Simplest way to insert a related row for each row in a table and link them together?

Question

What is the simplest way to insert a Car for each user in Users and set the users CarID to the ID of the inserted Car?

[Users]

- ID
- Name
- CarID

[Cars]

- ID (Auto increment)
- Name

Sorry if this might be a duplicate question but I can't find any simple solutions. Everything I've found is using complicated cursors, pointers etc.

A simple and reusable syntax for this would save me hours while migrating data during system upgrades etc.

The 'simplest' way would be to not use a system generated value to associate users with cars but instead use something that exists in the real world outside of the database, verifiable in reality, familiar to users, etc perhaps licence plate number or VIN. (Your cars have names?! perhaps use those :) — onedaywhen, Feb 17 '12 at 11:37

score 1 · Accepted Answer · edited Jan 18 '21 at 12:31

If you are on SQL Server 2008 or later you can use merge and output something like this.

Sample tables and data:

declare @Users table
(
  ID int identity primary key,
  Name varchar(10) not null,
  CarID int null
);

declare @Cars table
(
  ID int identity primary key,
  Name varchar(10) not null
);

insert into @Users(Name) values ('User1'),('User2'),('User3');

Add one care for each user and move the auto-generated CarID back to Users.

declare @ID table(CarID int, UserID int)

merge @Cars as C
using @Users as U
on 0 = 1
when not matched then
  insert (Name) values ('CarName')
output inserted.ID, U.ID into @ID;

update U
set CarID = I.CarID
from @Users as U
  inner join @ID as I
    on U.ID = I.UserID

Try it out on SE Data.

More info on the merge/output trick can be found here.

This is exactly what i was looking for :) Thanks! +1 for the nice example in SE Data. — Jonas Stensved, Feb 17 '12 at 10:48

score 0 · Answer 2 · answered Feb 17 '12 at 10:18

I'm assuming the code you're using lets you call stored procedures?

create procedure dbo.CarUserInsert_sp
(
@p_UserID int,
@p_UserName varchar(100),
@p_CarID int,
@p_CarName  varchar(100)
)
as

if not exists ( select 1 from Cars where ID = @p_CarID )
  insert Cars values ( @p_CarID, @p_CarName )

if exists (select 1 from Users where ID = @p_UserID )
  update Users set Name = @p_UserName, CarID = @p_CarID where ID = @p_UserID
else 
  insert Users values ( @p_UserID, @p_UserName,  @p_CarID )

go

Diego · Answer 3 · 2012-02-17T10:40:27.717

0

try this:

insert into cars (name)
select distinct(name) from users

update user 
set carId = (select ID from cars where cars.name=user.Name)

edited Feb 17 '12 at 10:40

answered Feb 17 '12 at 10:18

Diego

34,802
21
91
134

FYI, I honestly didn't understand very well what you need. It was just a guess – Diego Feb 17 '12 at 10:24
Yes but Cars doesn't have a userID? – Jonas Stensved Feb 17 '12 at 10:25
again, not sure what you nedd. If the carId is autoincrement do you want to insert records in Cars with the same name as the user name? If yes, I updated my code to do that – Diego Feb 17 '12 at 10:42

Simplest way to insert a related row for each row in a table and link them together?

3 Answers3