Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i used two table with different columns to insert into @temporary table. unable to complete task.
SQL
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
Posted
Updated 3-Oct-12 21:41pm
v2
Comments
skydger 4-Oct-12 16:32pm    
Is there any relationship between first and second table? Any column or calculated column.

1 solution

rewrite your insert like this:

SQL
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
 
Share this answer
 
Comments
Umapathi K 4-Oct-12 3:46am    
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

error like this
Guirec 4-Oct-12 6:32am    
your second select should return values for columns paid_amt, payment_mode, etc... (even if null values)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900