如何在临时表中合并两个不同的表 [英] how to merge two different table in temporary table
本文介绍了如何在临时表中合并两个不同的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用两个具有不同列的表插入@temporary表.无法完成任务.
i used two table with different columns to insert into @temporary table. unable to complete task.
DECLARE @Temptable as TABLE
(
--Customer Details
Cusname nvarchar(250),
Address1 nvarchar(250),
Address2 nvarchar(250),
Arr_Date datetime,
Dept_Date datetime,
TotalDays bigint,
Roomtype nvarchar(250),
Paid_Amt bigint,
Payment_Mode nvarchar(250),
taxamt bigint,
otherexpenses bigint,
roomrent bigint
----Hote Address Details
hotelname nvarchar(250),
haddress1 nvarchar(250),
haddress2 nvarchar(250),
city nvarchar(100),
state nvarchar(100),
pincode bigint,
phoneno bigint,
email_id nvarchar(150),
website nvarchar(150)
)
INSERT INTO @Temptable
-- First Table
SELECT t1.cusname,t1.address1,t1.address2,
CONVERT(VARCHAR,t2.arrdate,103)AS ArrivedDate,CONVERT(VARCHAR,t2.depdate,103) AS DEPTDATE,
t2.totaldays,t3.roomtype,t4.paid_amt,t4.payment_mode,
(SELECT taxvalue FROM taxdetails WHERE convert(VARCHAR,choosed_date,103)=
(SELECT max(convert(VARCHAR,choosed_date,103)) FROM taxdetails)) AS Tax_VALUE,
(SELECT sum(rate) from otherexpenses where bkid=@bookid) AS OT_Expenses,
(SELECT totamt FROM roombooked WHERE bookid=@bookid) AS RoomRent
FROM checkedout_details t4
left outer join roombooked t2 ON t4.book_id=t2.bookid left outer join roomdetails t3 ON
t3.rid=t2.rid left outer join
cusdetails t1 ON t1.cusid=t2.cusid WHERE t4.book_id=@bookid,
-- Second Table
SELECT
hotel_name,address1,address2,city,state,pincode,phone_no,email_id,website
FROM hotel_details
SELECT * FROM @Temptable
END
推荐答案
像这样重写您的插入内容:
rewrite your insert like this:
INSERT INTO @Temptable
-- First Table
SELECT t1.cusname,t1.address1,t1.address2,
CONVERT(VARCHAR,t2.arrdate,103)AS ArrivedDate,CONVERT(VARCHAR,t2.depdate,103) AS DEPTDATE,
t2.totaldays,t3.roomtype,t4.paid_amt,t4.payment_mode,
(SELECT taxvalue FROM taxdetails WHERE convert(VARCHAR,choosed_date,103)=
(SELECT max(convert(VARCHAR,choosed_date,103)) FROM taxdetails)) AS Tax_VALUE,
(SELECT sum(rate) from otherexpenses where bkid=@bookid) AS OT_Expenses,
(SELECT totamt FROM roombooked WHERE bookid=@bookid) AS RoomRent
FROM checkedout_details t4
left outer join roombooked t2 ON t4.book_id=t2.bookid left outer join roomdetails t3 ON
t3.rid=t2.rid left outer join
cusdetails t1 ON t1.cusid=t2.cusid WHERE t4.book_id=@bookid
UNION ALL
-- Second Table
SELECT
hotel_name,address1,address2,city,state,pincode,phone_no,email_id,website
FROM hotel_details
SELECT * FROM @Temptable
这篇关于如何在临时表中合并两个不同的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文