insert multiple table data in a temp table

Question

I have a multiple records from multiple table. I need to insert that records into temp table which i later pass on. The problem is when i insert rows into temp table it inserted data row wise means for every column row id counter increases. I need to insert data column wise. Means for every column (for which i fetch records from diff table) rowId counter starts from 1.if i insert data in 1st column my row id counter is 1-5(if 5 rows are there). If i fired 2nd statement (for 2nd column) data inserted into temp table from row id 6. and remaining (1-5) are null values. Is there any solution. Please help me. Any clues is appreciable. Thanks in advance! Below is my procedure...

alter PROCEDURE procGetPreviousOrderDetails --(@userid nvarchar(100))

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @recentViewedProduct nvarchar(max)
declare @AccountCreatedDate nvarchar(100)
declare @recentViewedCategories nvarchar(max)
declare @recentOrderProductList nvarchar(max)
declare @dateOflastPurchase nvarchar(max)
declare @shippingMethodList nvarchar(max)
declare @paymentMethodList nvarchar(max)
declare @totalOrderAmount nvarchar(100)
declare @zipCode nvarchar(100)

declare @tblOrderDetails table 
(
    RowId int identity(1,1),
    recentViewedProduct nvarchar(max),
    trailType int,
    AccountCreatedDate nvarchar(100),
    recentViewedCategories nvarchar(max),
    recentOrderProductList nvarchar(max),
    dateOflastPurchase nvarchar(max),
    shippingMethodList nvarchar(max),
    paymentMethodList nvarchar(max),
    totalOrderAmount nvarchar(100),
    zipCode nvarchar(100) 

)

if (select count(@@rowcount) from @tblOrderDetails) > 0   
begin                    
    truncate table tblOrderDetails 
end



 insert into  @tblOrderDetails(recentViewedProduct) select RV.item_name, RV.trail_type    from tblCMRecentlyViewed RV where RV.trail_type in (1, 2) and RV.user_id = '807CF717-9413-4CC3-8982-FF63A6B31906' order by RV.date_added desc
 insert into  @tblOrderDetails(AccountCreatedDate) select d_date_registered from UserObject where g_user_id = '807CF717-9413-4CC3-8982-FF63A6B31906'
  select distinct product_id from OrderFormLineItems where ordergroup_id = (select TOP 1 ordergroup_id from Ordergroup where user_id='807CF717-9413-4CC3-8982-FF63A6B31906' 
order by order_create_date desc)
select TOP 1 order_create_date from Ordergroup where user_id='807CF717-9413-4CC3-8982-FF63A6B31906' order by order_create_date desc
select distinct display_name from  ShipMethods where carrier_ship_method_id = (select distinct ship_method_id from Ordergroup where user_id='328239F8-A983-442C-A9E6-ADC203D5F52B')
select distinct saved_payment_type from OrderFormHeader where ordergroup_id in (select distinct ordergroup_id from Ordergroup where user_id='328239F8-A983-442C-A9E6-ADC203D5F52B')
select SUM(saved_cy_total_total) from Ordergroup where user_id='807CF717-9413-4CC3-8982-FF63A6B31906' 
select distinct u_postal_code from Addresses where g_id = '807CF717-9413-4CC3-8982-FF63A6B31906'

Thanks!

Patrik Melander · Accepted Answer · 2012-12-17 08:42:27Z

I would say you are going about this the wrong way. Each one of these queries should be a procedure in its own right. Merging the data should be the responsibility of the business logic.

You should consider returning AccountCreatedDate, DateOfLastPurchase and TotalOrderAmmount in one procedure, since it will only be one row, and the rest should have their own procedures.

If you REALLY want to do this you will need to do a LEFT JOIN on each sub select and do everything in a single query. But performance WILL suffer:

insert into @tblOrderDetails
select u.user_id, u.g_date_registered 
from UserObject u 
left join RV on u.user_id = RV.user_id 
    and RV.trail_type in (1, 2)
left join (
    select user_id, product_id 
    from OrderFormLineItems 
    where ordergroup_id = (
        select TOP 1 ordergroup_id 
        from Ordergroup 
        where user_id='807CF717-9413-4CC3-8982-FF63A6B31906')   
    ) ofli on ofli.user_id = u.user_id
left join (
    select TOP 1 user_id, order_create_date 
    from Ordergroup 
    where user_id='807CF717-9413-4CC3-8982-FF63A6B31906' 
    order by order_create_date desc
    ) og on og.user_id = u.user_id
left join (
    select distinct user_id, display_name 
    from  ShipMethods 
    where carrier_ship_method_id = (
        select distinct ship_method_id 
        from Ordergroup 
        where user_id='328239F8-A983-442C-A9E6-ADC203D5F52B')
    ) sm on sm.user_id = u.user_id
-- etc ...
where u.user_id = '328239F8-A983-442C-A9E6-ADC203D5F52B'

Jonathan Leffler · Accepted Answer · 2012-12-19 15:36:50Z

1

After you've inserted the rows with the first INSERT, you have to UPDATE rows to change the other columns in the rows. That means your temporary table must contain the relevant identifying information for each row so that you can apply the UPDATES accurately.

Alternatively, combine the SELECT statements into one, as suggested by Patrik Melander; this means you INSERT only complete records.

Both mechanisms avoid the trouble you're running into.

answered Dec 19, 2012 at 15:36

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Collectives™ on Stack Overflow

insert multiple table data in a temp table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related