Post reply

Set based approach in Store Procedure

  • June 9, 2010 at 4:37 am

    #219760

    Hello Team,

    I have a store procedure which is used to insert the data into my main table. I my procedure i compare the data from main table and temp tabel and insert the data into main table.

    Now I have temp table whit following schema and data

    -- drop table #hold

    create table #hold

    (

    scrp_nmvarchar(100),

    trn_dt datetime,

    trans_typ char(1),

    flgvARCHAR(5),

    Qty numeric(18,4),

    buy_sell_rtnumeric(18,4),

    tot_costnumeric(18,4),

    mkt_rt numeric(18,4),

    mkt_val numeric(18,4)

    )

    insert into #hold

    SELECT 'Zee News Limited', '18-Dec-2006', 'B', 'DMG', 904,49.97,45172.88,49.97, 45172.88

    UNION

    SELECT 'Zee Entertainment Ent. Ltd', '18-Dec-2006', 'B', 'DMG',2000,232.98,465960,232.98, 465960

    UNION

    SELECT 'Zee Entertainment Ent. Ltd', '18-Dec-2006', 'S', 'DMG', 2000,301.95,603900,301.95, 603900

    UNION

    SELECT 'Zee Entertainment Ent. Ltd', '12-Feb-2007', 'B', 'DMG', 2000,232.98,465960,232.98, 465960

    UNION

    SELECT 'Zee Entertainment Ent. Ltd', '12-Feb-2007', 'S', 'DMG', 2000,211.99,423980,211.99, 423980

    UNION

    SELECT 'Dish TV India Limited', '12-Feb-2007', 'B', 'DMG', 1150,36.51,41986.5,36.51, 41986.5

    Select DENSE_RANK() over (order by trn_dt ) DENSE_RANK, *

    from #hold

    Now First i want insert data which have DENSE_RANK 1 and after inserting 1 second will be inserting so 1 set will be available for 2nd batch. Is there any way to achieve this with out while or cursor

  • June 9, 2010 at 6:43 am

    #1177934

    Does this work for you?

    I'm not sure that I follow what it is you are attempting. Why not just insert all the data in one set?

    ;WITH CTE AS

    (

    Select DR = DENSE_RANK() over (order by trn_dt ), *

    from #hold

    )

    INSERT INTO dbo.MyMainTable (columns)

    SELECT (columns)

    FROM CTE

    WHERE DR = 1

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 9, 2010 at 11:44 am

    #1178152

    Thankx Wayan I had done the same thing.

    Thankx Again

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply