This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
asked
Dynamic SQL While Loop Not Executing
Hi there, I'm trying to split a table into another table in a number of batches using dynamic SQL and a WHILE loop however the variable appears to be incorrect? DROP TABLE RPT_SMS_FINAL_DUMP_BATCH IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='u' AND NAME='RPT_SMS_FINAL_DUMP_BATCH') CREATE TABLE RPT_SMS_FINAL_DUMP_BATCH( LKTL_CLIENTNO VARCHAR(10), SMSNUM VARCHAR(20), SALUTATION VARCHAR(75), BATCH INT) DECLARE @sql VARCHAR(8000) SELECT @sql = ' DECLARE @start INT DECLARE @num INT DECLARE @rownum INT SET @num = 5 SET @start = 1 SELECT @rownum = COUNT(*) / @num FROM RPT_SMS_FINAL_DUMP WHILE (@start
You need to set @rownum externally to the dynamic SQL, but then you have to define @num inside and outside of the dynamic SQL if you use EXEC(@SQL). Like this: DECLARE @sql VARCHAR(8000) DECLARE @rownum INT DECLARE @num INT SET @num = 5 SELECT @rownum = COUNT(*) / @num FROM RPT_SMS_FINAL_DUMP SELECT @sql = ' DECLARE @start INT DECLARE @num INT SET @num = 5 SET @start = 1 WHILE (@start
Thanks, managed to work it out: DROP TABLE RPT_SMS_FINAL_DUMP_BATCH IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='u' AND NAME='RPT_SMS_FINAL_DUMP_BATCH') CREATE TABLE RPT_SMS_FINAL_DUMP_BATCH( LKTL_CLIENTNO VARCHAR(10), SMSNUM VARCHAR(20), SALUTATION VARCHAR(75), BATCH INT) DECLARE @sql VARCHAR(8000) DECLARE @start INT DECLARE @num INT DECLARE @rownum INT SET @num = 6 SET @start = 1 SELECT @rownum = COUNT(*) / @num FROM RPT_SMS_FINAL_DUMP SET @sql = N' INSERT INTO RPT_SMS_FINAL_DUMP_BATCH(LKTL_CLIENTNO,SMSNUM,SALUTATION,BATCH) SELECT TOP ' + CAST(@rownum AS NVARCHAR(4)) + ' LKTL_CLIENTNO,SMSNUM,SALUTATION,0 FROM RPT_SMS_FINAL_DUMP WHERE LKTL_CLIENTNO NOT IN (SELECT LKTL_CLIENTNO FROM RPT_SMS_FINAL_DUMP_BATCH) ' WHILE (@start
Be careful with that divide of the row count - it implicitly rounds down the result. Because of the way that you're using the result, you'll lose records in the final table (unless your count is perfectly divisible by 6 that is): select 2000/6 = 333 select 333 * 6 = 1998 Also, LKTL_CLIENTNO has to be unique within the table, otherwise you might miss records as a result of your where clause if LKTL_CLIENTNO groups split across batches.
16 People are following this question.
