如何在临时表中合并两个不同的表 [英] 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屋！