Iterate Through Array Of Data In SQL Query

Question

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)

Virginia C · Answer

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 C · Answer

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