0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Supakin
  • 141
  • 5
  • 14
  • 1
    a- Did you realy mean that many employees have the same phone number? b- Why did you tag the question as `oracle` and `mysql` ? – A.B.Cade Mar 06 '13 at 06:23
  • @A.B.Cade if you see the insert query inside select statement, the phone numbers are from the whole table. i.e. each employee have their own phone number but unfortunately both fields are in same table. now i want to split that into two table and have a relatioinship may be Foreign key. – John Supakin Mar 06 '13 at 06:30

3 Answers3

1

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

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • That was great. One last issue is the user3 and user4 has same phone number and pointing to same ph_id key. There should be two different records even if the number is same. in future the user4 or user3 might update their number and that shouldn't affect other's record. User won't have same number but for some reason, this is my requirement. Could you please help me to complete my task. – John Supakin Mar 06 '13 at 07:21
0

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)
Doruk
  • 884
  • 9
  • 25
  • thanks for the query. you did hardcoded the phone number and employee name. the issue is we have to get the values from a select statement. – John Supakin Mar 06 '13 at 06:31
  • 2
    I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Mar 06 '13 at 06:35
  • Thanks, name and phone number are in single table. lets say "whole_table". i just updated my description, please look there for more details. – John Supakin Mar 06 '13 at 06:36
0

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

navi
  • 179
  • 1
  • 7