使用基于集合的条件插入表中 [英] to insert into the table using set based condition

问题描述

我试图从旧表中插入记录 使用游标进入新表，但是只插入少量记录需要太长时间。所以我正在寻找帮助来创建一个基于集合的sql来将旧表中的记录插入到新表中。

I was trying to insert the record from the old table  into the new table using cursor , but is taking too long to insert only few number of records. So I am looking for the help to create a set based sql to insert the record from old table into new.

这是我的OLD表

Use OLDdatabase
OLDAttTbl
Create Table OLDAttTbl
(
DepotID int Not Null,
DepartmentID int Not null,
EmpId int Not Null,
Attdate datetime Not Null,
IsAttendance int Not Null,
NormalHrs decimal(5,2) Not Null,
EmpSalary money Null)

OLDEmpTbl
Create table OLDEmpTbl
 (
Empid int not null,
EmpName nvarchar(100))

My新表

Use Newdatabase

Create NewAttTbl(
DepotID int Not Null,
DepartmentID int Not null,
NewEmpId int Not Null,
Attdate datetime Not Null,
IsAttendance bit  Not Null,
NormalHrs decimal(5,2) Not Null,
EmpSalary decimal(5,2) Null)

Create NewEmpTbl(
NewEmpid int notnull,
OLDEmpid int notnull,
EmpName nvarchar(100))

我试图使用基于集合的sql插入。请帮助你如何写它 

I am trying to insert using the set based sql . Please can you help how to write it 

Insert into NewAttTbl(
DepotID,
DepartmentID ,
NewEmpId ,
Attdate 
IsAttendance ,
NormalHrs ,
EmpSalary )
Values
(OLDAttTbl.depotid,
OLDAttTbl.department,
(NewEmptbl.Newempid where NewEmptbl.OLDEmpid = OLDemptbl.Empid), - New Empid has to be inserted for the old employee
OLDemptbl.AttDate,
OLDemptbl.IsAttendance, -- New table is bit  , old table it is int
OLDemptbl.NormalHrs
OLDemptblEmpsalary -- Old Table it is money in new table is decimal. 

 

我的记录如下

polachan

推荐答案

你可以简单地这样做

you can simply do this

INSERT NewEmpTbl (OLDEmpid,EmpName)
SELECT Empid,EmpName
FROM OLDEmpTbl

INSERT NewAttTbl (DepotId,DepartmentID,NewEmpID,AttDate,IsAttendance,NormalHrs,EmpSalary)
SELECT o.DepotId,o.DepartmentID,n.NewEmpid,o.AttDate,o.IsAttendance,o.NormalHrs,o.EmpSalary
FROM OldAttTbl o
JOIN NewEmpTbl n
ON n.OLDEmpid = o.EmpId

这篇关于使用基于集合的条件插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！