Casey Casey - 23 days ago 7
SQL Question

Looping through a select query to run updates by copying other row entries in SQL

So, i am needing to replicate some data that already exists in a table. For example, a users table could have one user, but that one user may have 20 entries for 20 different accounts. So what i am currently doing when i have a new user that they say need to replcate a different user, is i am doing a insert into table_a (select from table_a where UserID = 'user to replicate). Now instaed of running this once for each entry, how can i tell it to loop through multiple rows returned, and insert for each one.

Example:

insert into DocSS (Username, Password, CustID, CURQ, CUPO, CUPL, CUCC, DYQT, DYRR, DYCT, DYWS, DYST, DYON, DYDT, DYQA, DYND, DYCO, DYCA, DYPO, DYIN, DYCH, DYAI, DYAP, DYCC, DYMS, OSCO, OSRM, OSCT, OSDT, OSIN, OSPL, UserAdmin, DYPL, DYVL, OSQT, RTEN, RTOT, RTDP, RTCF, RTFN, RTCS, RTQT, OnlineQuoteID, MonthlyRpt, RTER, OnlineQuoteID2, OnlineQuoteID3, ViewAllAccounts)
select 'the_new_userID', 'the_new_userID', CustID, CURQ, CUPO, CUPL, CUCC, DYQT, DYRR, DYCT, DYWS, DYST, DYON, DYDT, DYQA, DYND, DYCO, DYCA, DYPO, DYIN, DYCH, DYAI, DYAP, DYCC, DYMS, OSCO, OSRM, OSCT, OSDT, OSIN, OSPL, UserAdmin, DYPL, DYVL, OSQT, RTEN, RTOT, RTDP, RTCF, RTFN, RTCS, RTQT, OnlineQuoteID, MonthlyRpt, RTER, OnlineQuoteID2, OnlineQuoteID3, ViewAllAccounts
from DocSS
where UserName = 'user_to_replicate' and CustID = '120180'


Now instead of having to enter in 20 more custID's and running it each time, how can i do this same technique but without using a CustID where clause, and loop though it for each returned row?

Answer Source

You do not want to enter userId and customer name you can simply leave it in where clause, as i have removed where clause completely

insert into DocSS (Username, Password, CustID, CURQ, CUPO, CUPL, CUCC, DYQT, DYRR, DYCT, DYWS, DYST, DYON, DYDT, DYQA, DYND, DYCO, DYCA, DYPO, DYIN, DYCH, DYAI, DYAP, DYCC, DYMS, OSCO, OSRM, OSCT, OSDT, OSIN, OSPL, UserAdmin, DYPL, DYVL, OSQT, RTEN, RTOT, RTDP, RTCF, RTFN, RTCS, RTQT, OnlineQuoteID, MonthlyRpt, RTER, OnlineQuoteID2, OnlineQuoteID3, ViewAllAccounts)
select 'the_new_userID', 'the_new_userID', CustID, CURQ, CUPO, CUPL, CUCC, DYQT, DYRR, DYCT, DYWS, DYST, DYON, DYDT, DYQA, DYND, DYCO, DYCA, DYPO, DYIN, DYCH, DYAI, DYAP, DYCC, DYMS, OSCO, OSRM, OSCT, OSDT, OSIN, OSPL, UserAdmin, DYPL, DYVL, OSQT, RTEN, RTOT, RTDP, RTCF, RTFN, RTCS, RTQT, OnlineQuoteID, MonthlyRpt, RTER, OnlineQuoteID2, OnlineQuoteID3, ViewAllAccounts
    from DocSS ;