How to duplicate a record Nntries under a new primary Key and user id

• john.fp

  Grasshopper

  Points: 14

  More actions

  August 2, 2017 at 3:20 am

  #343938

  I have a table that keeps a line of data for each page a user has access to and what permissions are set on that page.
  So for each user I could have numerous lines in the table. The primary Key in the table is the record entry. there is an Inner join with another table that holds the data for each page so that in this table the only info shown is the page number.

  Page_No	Record_Entry	Userid	Data_a	Data_b	Data_c	Data_d	Data_e	Data_f	Data_g	Data_h
  12	8068	GH01	0	0	0	0	0	0	0	0
  13	8067	GH01	0	0	0	0	0	0	0	0
  14	8069	GH01	0	0	0	0	0	0	0	0
  15	8056	GH01	0	0	0	0	0	0	0	0
  16	8055	GH01	0	0	0	0	0	0	0	0
  17	8057	GH01	0	0	0	0	0	0	0	0
  18	28653	GH01	0	1	0	1	2	0	2	0
  21	8063	GH01	0	1	0	1	1	2	0	0
  22	24869	GH01	0	0	0	0	1	2	0	0
  23	8058	GH01	0	1	0	1	1	0	0	0

  I am trying to write a script that will create a duplicat entry for eah line but under a new userid and record entry. (basically duplicate someones permissions for another user. I have tried numerous methods but do not seem to be able to get  a script to copy each line selecting the next available record_entry number and a supplied userid.

  Any advice or help woyl be appreciated

• Thom A

  SSC Guru

  Points: 99143

  More actions

  August 2, 2017 at 3:47 am

  #1953722

  Without DDL and consumable sample data (so hasn't been tested):
  DECLARE @NewUserID int, @CopyUserID int;

  INSERT INTO YourTable
SELECT Page_No, Record_entry,
       @NewUserID,
       Data_a, Data_b, Data_c, Data_d, Data_e, Data_f, Data_g, Data_h
FROM YourTable
WHERE Userid = @CopyUserID;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738773

  More actions

  August 2, 2017 at 9:53 am

  #1953808

  Use something like Thom's script. Don't work on a row. Work on a group at a time.