0

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?

Casey ScriptFu Pharr
  • 1,672
  • 1
  • 16
  • 36
  • 2
    I guess I must not be understanding your issue. It seems to me that you could either leave off the `and custid=...` completely, or use `and custid in '120180', '120191', ...)` – Brian Leach Sep 26 '17 at 17:29
  • Are you want what Brian Leach suggested or custid in (select custid from table where usersid =12346) – Rams Sep 26 '17 at 17:34
  • possible duplicate of https://stackoverflow.com/questions/25969/insert-into-values-select-from – 1010 Sep 26 '17 at 17:50
  • So if i do not enter a WHERE clause for CustID, and the user i am replicating has 20 rows for 20 different cust id's, this query will insert to rows for the replicated user with one run? – Casey ScriptFu Pharr Sep 26 '17 at 17:52
  • @Casey Yes, that is exactly what will happen. – Tab Alleman Sep 26 '17 at 17:58

1 Answers1

1

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 ;
Guru0008
  • 54
  • 1