将所选数据从多个表插入到单个表中

I have following table structure in SQL Server 2005.

我在SQL Server 2005中有以下表结构。

AttendanceDetail (AttenID, Date, EmpID,....)

AttendanceDetail(AttenID,Date,EmpID,....)

A_OfficialDetail (AttenID, OfficeOut, OfficeIn)

A_OfficialDetail(AttenID,OfficeOut,OfficeIn)

A_Personal Detail (AttenID, OfficeOut, OfficeIn)

A_Personal详细信息(AttenID,OfficeOut,OfficeIn)

A_RecessDetail (AttenID, OfficeOut, OfficeIn)

A_RecessDetail(AttenID,OfficeOut,OfficeIn)

Atten_Temp_AttenDetail** (EmpID, AttenID, Detail, OfficeOut, OfficeIn)

Atten_Temp_AttenDetail **(EmpID,AttenID,Detail,OfficeOut,OfficeIn)

I need a pure SQL code to insert data into table 5 from table 1,2,3,4 such that:

我需要一个纯SQL代码将数据从表1,2,3,4插入表5,这样:

AttendanceDetail has EmpID=100, and Table 2,3,4 match the AttenID of AttendanceDetail as for EmpID=100.

AttendanceDetail的EmpID = 100,表2,3,4匹配AttendanceDetail的AttenID和EmpID = 100。

I tried it with a cursor and looping but couldn't get what I wish, it is inserting more records than I wish. Please Help Me. Thanks in Advance

我用光标和循环尝试了它,但无法得到我想要的,它插入的记录超出了我的意愿。请帮我。提前致谢

OfficeOut and OfficeIn should be inserted from 3 tables A_OfficialDetail,A_recessdetail and A_PersonalDetail According to AttenID in AttendanceDetail. The main purpose is to insert data of Official Out-in,Recess Out-in and Personal Out-in in on Atten_Temp_AttenDetail Table. The table should actually look like:

应根据AttendanceDetail中的AttenID,从3个表A_OfficialDetail,A_recessdetail和A_PersonalDetail中插入OfficeOut和OfficeIn。主要目的是在Atten_Temp_AttenDetail表中插入Official Out-in,Recess Out-in和Personal Out-in的数据。该表应该看起来像:

==================================
UserID  AttenID  Date      Remark   OfficeOut  OficeIN

100      12      7/8/2011  Office   11:00       12:00

100      12      7/8/2011  Office   13:45        14:00

The code I tried is

我试过的代码是

Declare @AttenID bigint

Open cur

Fetch Next from cur into @AttenID

while @@fetch_status=0

begin

insert into Atten_Temp_AttenDetail(userID,AttenID,Date,OfficeOut,OfficeIn)
select @AttenID,A.workingDate,OfficeOut,OfficeIn from A_OfficialDetail as O,AttendanceDetail as A where O.AttenID=@AttenID and A.AttenID=@AttenID

insert into Atten_Temp_AttenDetail(userID,AttenID,Date,OfficeOut,OfficeIn)
select @AttenID,A.workingDate,OfficeOut,OfficeIn from A_PersonalDetail as O,AttendanceDetail as A where O.AttenID=@AttenID and A.AttenID=@AttenID


fetch next from cur into @AttenID

end

close cur

Deallocate cur

2 个解决方案

#1

This looks like it is really simple really....

这看起来真的很简单....

INSERT INTO Atten_Temp_AttenDetail 
SELECT 
a.EmpID, 
a.AttenID,
'', -- You have no fields for this in the database, may be in AttendanceDetail
b.OfficeOut, -- With the next two, not sure if you want to use A_OfficialDetail or A_Personal Detail
c.OfficeIn
FROM AttendanceDetail a
INNER JOIN A_OfficialDetail b ON a.AttenID = b.AttenID
INNER JOIN [A_Personal Detail] c ON a.AttenID = c.AttenID
INNER JOIN A_RecessDetail d on a.AttenID = d.AttenID
WHERE a.EmpID = 100

This what you are after?

这就是你的追求?

#2

I would start by making a query, perhaps by using joins, that gives me the result that should be saved.

我首先要做一个查询,也许是通过使用连接,这给了我应该保存的结果。

Then it would merely just be a kind of select into, in order to save the data in Atten_Temp_AttenDetail.

然后它只是一种选择,以便在Atten_Temp_AttenDetail中保存数据。

How the SQL and join should be, depends on how the relation between your tables are

SQL和连接应该如何,取决于表之间的关系

Update: If table 2,3,4 are more or less identical, then how about using UNION?

更新:如果表2,3,4或多或少相同,那么如何使用UNION?

#1