CODEWITHSUNDEEP
sqlsql-serversql-server-2008
John Supakin
John Supakin

Reputation: 141

Insert a record from select statement and bring back identity to same select query

I have an issue with a SQL query

I have a table which has two columns, employee_name, phone_number. This table has 100 rows.

I want to store employee_name in one table and phone_number in another table and have a relationship. I will explain the schema

EmployeeTable

emp_id (identity)
emp_name (text)
ph_id (FK from the phone table)

phone table

ph_id (identity)
phone_number

So I tried

insert into emp_table (emp_name, ph_id)
select employee_name, (insert into phone_table output inserted.ph_id values (whole.phone)) as phone_id

this query is wrong. But this is what I am trying to do.

I want to insert phone number into phone number table and have the identity in the same select statement and insert into the employee table.

Your thoughts?

The final table should be

Whole table (This is what I have now)

emp_name   phone_number
----------------------
name1      123
name2      456
name4      789

and after query executed

employee_table

name    ph_id
-------------
name1   1
name2   2
name3   3

phone_table

phone_id   phone_number
-------------------------
1          123
2          456
3          789

Upvotes: 0

Views: 292

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Not sure that you have your target table structure the way you should but this will do the job with what is in the question.

First insert one row for each distinct phone number to phone_table and then insert into employee_table with a join between whole_table and phone_table on phone_number

insert into phone_table(phone_number)  
select distinct phone_number
from whole_table

insert into employee_table(name, ph_id)
select w.emp_name, p.phone_id 
from whole_table as w
  inner join phone_table as p
    on w.phone_number = p.phone_number

SQL Fiddle

Update:

You can use merge ... output as described here to handle duplicates of phone numbers and names.

Capture the source data with the generated id in a table variable and use the table variable in the insert statement. 

declare @T table
(
  phone_id int,
  emp_name varchar(10),
  phone_number varchar(10)
);

merge phone_table as p
using whole_table as w 
on 0 = 1
when not matched then
insert (phone_number) values (w.phone_number)
output inserted.phone_id,
       w.emp_name,
       w.phone_number
into @T(phone_id, emp_name, phone_number);

insert into employee_table(name, ph_id)
select t.emp_name, t.phone_id 
from @T as t;

SQL Fiddle

Upvotes: 1

Doruk
Doruk

Reputation: 914

Use the @@Identity. Like this:

insert into phoneTable(phone_number) values('999888777');

declare @phoneid int

set @phoneid = @@IDENTITY

insert into EmployeeTable(emp_name, ph_id) values('John Supakin', @phoneid)

Or, simply this. But be careful with it in long queries.

insert into phoneTable(phone_number) values('999888777');

insert into EmployeeTable(emp_name, ph_id) values('John Supakin', @@IDENTITY)

Upvotes: 0

navi
navi

Reputation: 179

You will have to split into 2 different SQLs. One to insert into EmployeeTable and another for the phonetable.

Upvotes: 0

Related Questions