Looped insert statement in SQL Server 2008

Question

Any help that can be provided would be much appreciated.

I have the following two tables

TS_DEF_TRADER:

DEF_TRADER_ID  MANAGER_CD       INV_CLASS_CD    TRADER_CD
------         -------          ----------      ---------
101            HARRIT           EQTY            MMGR_DT
108            NAIDON           EQTY            MMGR_DT
123            MLONDG           EQTY            MMGR_DT

and PDF_USER_GROUP:

GRP_CD                    USER_CD       
------                    -------          
STANLIB_MULTI_MANAGER     HARRIT           
STANLIB_MULTI_MANAGER     NAIDON           
STANLIB_MULTI_MANAGER     MLONDG 
STANLIB_MULTI_MANAGER     FARRYM
STANLIB_MULTI_MANAGER     HOLMEM

What I want to do is create some kind of a looped SQL insert statement (if that's the right terminology) that will check PDF_USER_GROUP and if there is a USER_CD with GRP_CD = STANLIB_MULTI_MANAGER that is missing from TS_DEF_TRADER. If any anything is missing then it will insert the relevant row.

I've written the below statement:

declare @DefTraderID INT
declare @Mgr varchar(200)
set @DefTraderID = (select MAX(DEF_TRADER_ID) + 1  from TS_DEF_TRADER)
set @Mgr = (select min(USER_CD) from PDF_USER_GROUP where not exists 
(select * from TS_DEF_TRADER where 
TRADER_CD = 'MMGR_DT' and 
INV_CLASS_CD = 'EQTY' and
TS_DEF_TRADER.MANAGER_CD = PDF_USER_GROUP.USER_CD)
and GRP_CD = 'STANLIB_MULTI_MANAGER'  and USER_CD not in ('MMGR_DT', 'SLIB_INDEX'))


insert into TS_DEF_TRADER (DEF_TRADER_ID,MANAGER_CD,INV_CLASS_CD, TRADER_CD)
values (@DefTraderID,@Mgr, 'EQTY', 'MMGR_DT')

If I run this manually myself twice, I get the following result which is what I want:

DEF_TRADER_ID  MANAGER_CD       INV_CLASS_CD    TRADER_CD
------         -------          ----------      ---------
101            HARRIT           EQTY            MMGR_DT
108            NAIDON           EQTY            MMGR_DT
123            MLONDG           EQTY            MMGR_DT
124            FARRYM           EQTY            MMGR_DT
125            HOLMEM           EQTY            MMGR_DT

If I were to run it a third time this would happen:

DEF_TRADER_ID  MANAGER_CD       INV_CLASS_CD    TRADER_CD
------         -------          ----------      ---------
101            HARRIT           EQTY            MMGR_DT
108            NAIDON           EQTY            MMGR_DT
123            MLONDG           EQTY            MMGR_DT
124            FARRYM           EQTY            MMGR_DT
125            HOLMEM           EQTY            MMGR_DT
126            NULL             EQTY            MMGR_DT

I need it to run by itself (ie: loop) and stop once when it needs to so we don't get the NULLs.

To summerize what you want, If there is a PDF_User_Group.User_CD that doesnt exist in TS_def_Trader.Manager_CD and the PDF_User_Group.GRP_CD == STANLIB_MULTI_MANGER . You want to add it to TS_Def_trader? — gh9, Feb 04 '14 at 14:40

score 3 · Answer 1 · answered Feb 04 '14 at 14:50

This doesn't need a loop at all, you can just do a single INSERT statement:

DECLARE @MaxDefTraderId INT

SELECT @MaxDefTraderId = MAX(DEF_TRADER_ID)
FROM TS_DEF_TRADER

INSERT INTO TS_DEF_TRADER(DEF_TRADER_ID, MANAGER_CD, INV_CLASS_CD, TRADER_CD)
SELECT  @MaxDefTraderId + ROW_NUMBER() OVER(ORDER BY USER_CD) DEF_TRADER_ID, 
        USER_CD MANAGER_CD,
        'EQTY' INV_CLASS_CD,
        'MMGR_DT' TRADER_CD
FROM PDF_USER_GROUP A
WHERE GRP_CD = 'STANLIB_MULTI_MANAGER'
AND NOT EXISTS(SELECT 1 FROM TS_DEF_TRADER
               WHERE MANAGER_CD = A.USER_CD)

Here is a demo with this. And the results are:

╔═══════════════╦════════════╦══════════════╦═══════════╗
║ DEF_TRADER_ID ║ MANAGER_CD ║ INV_CLASS_CD ║ TRADER_CD ║
╠═══════════════╬════════════╬══════════════╬═══════════╣
║           101 ║ HARRIT     ║ EQTY         ║ MMGR_DT   ║
║           108 ║ NAIDON     ║ EQTY         ║ MMGR_DT   ║
║           123 ║ MLONDG     ║ EQTY         ║ MMGR_DT   ║
║           124 ║ FARRYM     ║ EQTY         ║ MMGR_DT   ║
║           125 ║ HOLMEM     ║ EQTY         ║ MMGR_DT   ║
╚═══════════════╩════════════╩══════════════╩═══════════╝

Now, I need to warn you about the way you are calculating the DEF_TRADER_ID. You should be using an IDENTITY column instead of assigning the value this way. What happens when another user tries to insert values to that table?, you could be inserting duplicates ids.

Thanks. Solutions work 100%. I agree with you with regards to the Identity column however the table is a system table so I don't want to add additional columns. DEF_TRADER_ID is a primary key however so duplicates shouldn't be allowed. — Andrew, Feb 05 '14 at 08:58

score 1 · Accepted Answer · edited May 23 '17 at 12:03

You can use a WHILE loop, and continually evaluate @Mgr for NULL:

declare @DefTraderID INT;
declare @Mgr varchar(200);

set @DefTraderID = (select MAX(DEF_TRADER_ID) + 1  from TS_DEF_TRADER);
set @Mgr = (select min(USER_CD) from PDF_USER_GROUP where not exists 
 (select * from TS_DEF_TRADER 
  where 
    TRADER_CD = 'MMGR_DT' and 
    INV_CLASS_CD = 'EQTY' and
    TS_DEF_TRADER.MANAGER_CD = PDF_USER_GROUP.USER_CD)
  and GRP_CD = 'STANLIB_MULTI_MANAGER'  
  and USER_CD not in ('MMGR_DT', 'SLIB_INDEX'));


WHILE (@Mgr IS NOT NULL)
  BEGIN

   insert into TS_DEF_TRADER (DEF_TRADER_ID,MANAGER_CD,INV_CLASS_CD, TRADER_CD)
   values (@DefTraderID, @Mgr, 'EQTY', 'MMGR_DT');

    set @DefTraderID = (select MAX(DEF_TRADER_ID) + 1  from TS_DEF_TRADER);
    set @Mgr = (select min(USER_CD) from PDF_USER_GROUP where not exists 
     (select * from TS_DEF_TRADER 
      where 
        TRADER_CD = 'MMGR_DT' and 
        INV_CLASS_CD = 'EQTY' and
        TS_DEF_TRADER.MANAGER_CD = PDF_USER_GROUP.USER_CD)
      and GRP_CD = 'STANLIB_MULTI_MANAGER'  
      and USER_CD not in ('MMGR_DT', 'SLIB_INDEX'));

  END

With the obvious caveat that @Mgr ought to be NULL at some point otherwise you'll loop forever.

AFAIK there is no DO..WHILE / REPEAT..UNTIL construct in SqlServer, however you can dry up the duplicated evaluation of @Mgr + @DefTrader with one of these workarounds ~GOTO :(

Also, it may be a good idea to reconsider the incrementing of @DefTrader, viz (select MAX(DEF_TRADER_ID) + 1 from TS_DEF_TRADER), e.g. with an IDENTITY column or a locked counters pattern - at the moment, the code isn't concurrent-safe.

Thanks. Solutions work 100%. I agree with you with regards to the Identity column however the table is a system table so I don't want to add additional columns. DEF_TRADER_ID is a primary key however so duplicates shouldn't be allowed. — Andrew, Feb 05 '14 at 09:00
Note that Lamak does have a good point, though - if at all possible, try and use a set-based approach to processing, as this will have performance and scalability benefits :-) — StuartLC, Feb 05 '14 at 09:09

Looped insert statement in SQL Server 2008

2 Answers2