Loading

Insert from temparay table

Insert from temparay table

lejo

3y825SQL Server

i have 3 rows in my temparay table .i want to insert one by one in master and child table .with primary key of master table in to child table.

when i inserting first row master key id is repeating all columnins in second table.

temp table structure

EmpName ChildName

Employee1 Student1

Employee1 Student2

Employee2 Student3

Employee3 Student4

i have to get result like below in tables

eg: Empid employeeName

1 Employee1

2 Employee2

3 Employee3

child table

ChildID EmpID ChildName

1 1 Student1

2 1 Student2

3 2 Student3

4 3 Student4

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aravind Govindaraj3y ago
Accepted answer
First Insert into Master Table with the group by Employee Id of Temp Table, So that you will get 3 results.

Secondly, Insert it into Child Table like below

Temp Table Join Master Table then

Child Id is the identity it is auto-generated | Get Emp Id from Master Table | Student Name from Temp Table
Avinash Sharma3y ago
With This you Get Your Desire Result

Create table #TmpData
(
Id Int identity(1,1) not null ,
EmpName varchar(50) null,
ChildName varchar(50) null
)

insert into #TmpData(EmpName,ChildName)
values('Employee1','Student1')
insert into #TmpData(EmpName,ChildName)
values('Employee1','Student2')
insert into #TmpData(EmpName,ChildName)
values('Employee2','Student3')
insert into #TmpData(EmpName,ChildName)
values('Employee3','Student4')

create table Employee
(
EmpId Int identity(1,1) not null ,
EmpName varchar(50) null,
PRIMARY KEY(EmpId)
)

create table Child
(
ChildID Int identity(1,1) not null,
ChildName varchar(50) null,
EmpID Int ,
PRIMARY KEY(ChildID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpId)
)

insert into Employee(EmpName)
select distinct EmpName from #TmpData

insert into Child(EmpID,ChildName)
select EmpId,ChildName from #TmpData t inner join
Employee e on e.EmpName=t.EmpName

select * from Employee
select * from Child