Iterate Through Array Of Data In SQL Query

Virginia C

I'm trying to apply an update to a very wide table. It has 250+ columns. My update statement works if I execute it in a sql window, but it's not working when I tried to implement it with iterating through an array. I used the article below as my guide. I feel like I'm very close, but I'm not sure what I'm missing.

I used Sarvesh Shinde's example article: iterate-through-array-of-data-in-sql-query. (Sorry, I haven't earned the reputation to post hyperlinks. lol)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Virginia C3y ago

CREATE TABLE EMPLOYEE(
EMPLOYEE_ID    INT,
EMPLOYEE_NAME  NVARCHAR(000),
JOB_FIELD      INT
)
GO
INSERT INTO EMPLOYEE VALUES (0, 'A', NULL);
INSERT INTO EMPLOYEE VALUES (2, 'B', NULL);
INSERT INTO EMPLOYEE VALUES (3, 'C', NULL);
INSERT INTO EMPLOYEE VALUES (4, 'D', NULL);
INSERT INTO EMPLOYEE VALUES (5, 'E', NULL);
INSERT INTO EMPLOYEE VALUES (6, 'F', NULL);
INSERT INTO EMPLOYEE VALUES (7, 'G', NULL);
INSERT INTO EMPLOYEE VALUES (8, 'H', NULL);
INSERT INTO EMPLOYEE VALUES (9, 'I', NULL);
INSERT INTO EMPLOYEE VALUES (10, 'J', NULL);
GO



CREATE TABLE RSA(
    EMPLOYEE_ID                       INT,
    job_explore_provd_inh             INT default 0,
    job_explore_provd_purch           INT default 0,
    job_explore_provr                 INT default 0,
    job_explore_title_i               INT default 0,
    job_explore_title_vi              INT default 0,
    job_explore_comp_bene_provr       INT default 0,
    job_explore_comp_bene             INT default 0,
    job_explore_pmt_amt               INT default 0,
    wrk_base_provd_inh                INT default 0,
    wrk_base_provd_purch              INT default 0,
    wrk_base_provr                    INT default 0,
    wrk_base_title_i                  INT default 0,
    wrk_base_title_vi                 INT default 0,
    wrk_base_comp_bene_provr          INT default 0,
    wrk_base_comp_bene                INT default 0,
    wrk_base_pmt_amt                  INT default 0,
    couns_enroll_provd_inh            INT default 0,
    couns_enroll_provd_purch          INT default 0,
    couns_enroll_provr                INT default 0,
    couns_enroll_title_i              INT default 0,
    couns_enroll_title_vi             INT default 0,
    couns_enroll_comp_bene_provr      INT default 0,
    couns_enroll_comp_bene            INT default 0,
    couns_enroll_pmt_amt              INT default 0,
    wrkplce_read_provd_inh            INT default 0,
    wrkplce_read_provd_purch          INT default 0,
    wrkplce_read_provr                INT default 0,
    wrkplce_read_title_i              INT default 0,
    wrkplce_read_title_vi             INT default 0,
    wrkplce_read_comp_bene_provr      INT default 0,
    wrkplce_read_comp_bene            INT default 0,
    wrkplce_read_pmt_amt              INT default 0,
    ins_self_advoc_provd_inh          INT default 0,
    ins_self_advoc_provd_purch        INT default 0,
    ins_self_advoc_provr              INT default 0,
    ins_self_advoc_title_i            INT default 0,
    ins_self_advoc_title_vi           INT default 0,
    ins_self_advoc_comp_bene_provr    INT default 0,
    ins_self_advoc_comp_bene          INT default 0,
    ins_self_advoc_pmt_amt            INT default 0
)
CREATE TABLE RSA_DUPE(
    EMPLOYEE_ID                       INT,
    job_explore_provd_inh             INT default 1,
    job_explore_provd_purch           INT default 1,
    job_explore_provr                 INT default 1,
    job_explore_title_i               INT default 1,
    job_explore_title_vi              INT default 1,
    job_explore_comp_bene_provr       INT default 1,
    job_explore_comp_bene             INT default 1,
    job_explore_pmt_amt               INT default 1,
    wrk_base_provd_inh                INT default 1,
    wrk_base_provd_purch              INT default 1,
    wrk_base_provr                    INT default 1,
    wrk_base_title_i                  INT default 1,
    wrk_base_title_vi                 INT default 1,
    wrk_base_comp_bene_provr          INT default 1,
    wrk_base_comp_bene                INT default 1,
    wrk_base_pmt_amt                  INT default 1,
    couns_enroll_provd_inh            INT default 1,
    couns_enroll_provd_purch          INT default 1,
    couns_enroll_provr                INT default 1,
    couns_enroll_title_i              INT default 1,
    couns_enroll_title_vi             INT default 1,
    couns_enroll_comp_bene_provr      INT default 1,
    couns_enroll_comp_bene            INT default 1,
    couns_enroll_pmt_amt              INT default 1,
    wrkplce_read_provd_inh            INT default 1,
    wrkplce_read_provd_purch          INT default 1,
    wrkplce_read_provr                INT default 1,
    wrkplce_read_title_i              INT default 1,
    wrkplce_read_title_vi             INT default 1,
    wrkplce_read_comp_bene_provr      INT default 1,
    wrkplce_read_comp_bene            INT default 1,
    wrkplce_read_pmt_amt              INT default 1,
    ins_self_advoc_provd_inh          INT default 1,
    ins_self_advoc_provd_purch        INT default 1,
    ins_self_advoc_provr              INT default 1,
    ins_self_advoc_title_i            INT default 1,
    ins_self_advoc_title_vi           INT default 1,
    ins_self_advoc_comp_bene_provr    INT default 1,
    ins_self_advoc_comp_bene          INT default 1,
    ins_self_advoc_pmt_amt            INT default 1
)



SELECT * FROM RSA;

INSERT INTO RSA
  (employee_id)
SELECT employee_id
FROM employee;

Virginia C3y ago

Use TestDb
GO
--DECLARE @PRODUCTDETAILSTABLE table (PRODUCTNAME nvarchar(100), PRODUCTID int, PRODUCTCOST int)

-- Declare your array table variable
DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )

-- Add values to your array table, these are the values which you need to look for in your database
INSERT INTO @MYARRAY (TEMPCOL)
   VALUES
('job_explore_'), ('wrk_base_'), ('couns_enroll_'), ('wrkplce_read_'), ('ins_self_advoc_')

--SELECT * FROM @MYARRAY

DECLARE @INDEXVAR int
DECLARE @TOTALCOUNT int
DECLARE @CURINDEXEDPRODUCTID nvarchar (50)
DECLARE @col1   varchar(50)
DECLARE @col2   varchar(50)
DECLARE @col3   varchar(50)
DECLARE @col4   varchar(50)
DECLARE @col5   varchar(50)
DECLARE @col6   varchar(50)
DECLARE @col7   varchar(50)
DECLARE @col8   varchar(50)

DECLARE @scol1   varchar(50)
DECLARE @scol2   varchar(50)
DECLARE @scol3   varchar(50)
DECLARE @scol4   varchar(50)
DECLARE @scol5   varchar(50)
DECLARE @scol6   varchar(50)
DECLARE @scol7   varchar(50)
DECLARE @scol8   varchar(50)

SET @INDEXVAR = 0
SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY
WHILE @INDEXVAR < @TOTALCOUNT

BEGIN
    SELECT @INDEXVAR = @INDEXVAR + 1

    -- Get value of current indexed product ID from array table
    SELECT @CURINDEXEDPRODUCTID = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR

    PRINT @INDEXVAR;
    --PRINT @CURINDEXEDPRODUCTID;

    --WHILE @INDEXVAR = 1
        BEGIN
          SET @col1 = CONCAT(@CURINDEXEDPRODUCTID, 'provd_inh')
          SET @scol1 = CONCAT('s.', @col1)
          SET @col2 = CONCAT(@CURINDEXEDPRODUCTID, 'provd_purch')
          SET @scol2 = CONCAT('s.', @col2)
          SET @col3 = CONCAT(@CURINDEXEDPRODUCTID, 'provr')
          SET @scol3 = CONCAT('s.', @col3)
          SET @col4 = CONCAT(@CURINDEXEDPRODUCTID, 'title_i')
          SET @scol4 = CONCAT('s.', @col4)
          SET @col5 = CONCAT(@CURINDEXEDPRODUCTID, 'title_vi')
          SET @scol5 = CONCAT('s.', @col5)
          SET @col6 = CONCAT(@CURINDEXEDPRODUCTID, 'comp_bene_provr')
          SET @scol6 = CONCAT('s.', @col6)
          SET @col7 = CONCAT(@CURINDEXEDPRODUCTID, 'comp_bene')
          SET @scol7 = CONCAT('s.', @col7)
          SET @col8 = CONCAT(@CURINDEXEDPRODUCTID, 'pmt_amt')
          SET @scol8 = CONCAT('s.', @col8)

          PRINT @col1;
          PRINT @scol1;
          PRINT @col2;
          PRINT @scol2;
          PRINT @col3;
          PRINT @col4;
          PRINT @col5;
          PRINT @col6;
          PRINT @col7;
          PRINT @col8;

            BEGIN
                UPDATE RSA 
                SET @col1 = @scol1,
                    @col2 = @scol2,
                    @col3 = @scol3,
                    @col4 = @scol4,
                    @col5 = @scol5,
                    @col6 = @scol6,
                    @col7 = @scol7,
                    @col8 = @scol8
                FROM dbo.RSA t JOIN dbo.RSA_DUPE s
                ON t.employee_id = s.employee_id
                --WHERE @INDEXVAR = 1
            END;

        END;
    -- Get details of Product matching current indexed product ID from array

        --SELECT * FROM RSA;  

END;