Duplicate a row in SQL?

Question

OK I have a table that has two columns, userID and courseID. It is used to assign training courses to a user. It looks like this:

userid   courseid
0          1
0          3
0          6
1          1
1          4
1          5

so user 0 is assigned to courses 1,3,6 and user 1 is assigned to 1, 4 5

anyways I need to take every user that is assigned to 6 and create a new row that has that userid and courseid 11, basically assigning every user who is currently assigned to 6 to also be assigned to 11

for some reason (I did not create this database) both rows are marked as primary keys, and some statements I have tried have thrown an error because of this, what the heck is the deal?

oh maybe it is because there are a few users that are already assigned to 11 so it is choking on those maybe?

please help

Answer 1

Insert Into TableName (userID, courseID)
  Select userID, 11 From TableName Where courseID=6;

Also, I'm a bit confused by your comment that both are primary keys. Both rows can be part of the primary key or both can be Unique keys but they cannot both be a primary key. As far as errors go, it is probably because the query tried to insert rows that were duplicates of already existing rows. To eliminate this possibility you could do this:

Insert Into TableName (userID, courseID)
  Select userID, 11 From TableName Where courseID=6 
     AND (userID not in (Select userID From TableName Where courseID=11))

Depending on your database this could work too:

INSERT OR IGNORE INTO TableName (userID, courseID)
    SELECT userID, 11 FROM TableName WHERE courseID=6;

Anyway, there you go.

Answer 2

This should help:

INSERT
INTO   [table]
       (
              userid,
              courseid
       )
SELECT userid,
       11
FROM   [table]
WHERE  courseid    = 6
   AND userid NOT IN
                     (SELECT userid
                     FROM    [table]
                     WHERE   courseid = 11
                     );

This will select all users in course 6 not in course 11 and add them with course 11 to the table.

Answer 3

insert into TableName (userId, courseId)
    select userId, 11
    from   TableName
    where  courseId = 6
    and    not exists (
               select 1
               from   TableName nested
               where  nested.userId = TableName.UserId
               and    nested.courseId = 11
           )

Selects all users that are assigned to courseId 6 but are not yet assigned to courseId 11 and inserts a new record into the table for them for courseId 11.

Answer 4

Maybe something like this?

SET IDENTITY_INSERT TABLE_NAME ON
-- Insert new rows here
SET IDENTITY_INSERT TABLE_NAME OFF