How to insert multiple row values into SQL

Question

I have the following 3 tables:

CREATE TABLE Tests (
    Test_ID INT,
    TestName VARCHAR(50));

INSERT INTO Tests VALUES (1, 'SQL Test');
INSERT INTO Tests VALUES (2, 'C# Test');
INSERT INTO Tests VALUES (3, 'Java Test');


CREATE TABLE Users (
    [User_ID] INT,
    UserName VARCHAR(50));

INSERT INTO Users VALUES (1, 'Joe');
INSERT INTO Users VALUES (2, 'Jack');
INSERT INTO Users VALUES (3, 'Jane');



CREATE TABLE UserTests (
    ID INT,
    [User_ID] INT,
    Test_ID INT,
    Completed INT);

INSERT INTO UserTests VALUES (1, 1, 1, 0);
INSERT INTO UserTests VALUES (2, 1, 2, 1);
INSERT INTO UserTests VALUES (3, 1, 3, 1);
INSERT INTO UserTests VALUES (4, 2, 1, 0);
INSERT INTO UserTests VALUES (5, 2, 2, 0);
INSERT INTO UserTests VALUES (6, 2, 3, 0);
INSERT INTO UserTests VALUES (7, 3, 1, 1);
INSERT INTO UserTests VALUES (8, 3, 2, 1);
INSERT INTO UserTests VALUES (9, 3, 3, 1);

I would like to create some rule/trigger so that when a new user gets added to the Users table, an entry for each Test and that user's Id will get added to the UserTests table.

Something like this if the new user ID is 5:

INSERT dbo.UserTest
       (USER_ID, TEST_ID, Completed)
VALUES 
       (5, SELECT TEST_ID FROM Tests, 0)

That syntax is of course wrong but to give an idea of what I expect to happen.

So I expect that statement to add these values to the UserTests table:

User ID| Test ID| Completed 
5      | 1      | 0
5      | 2      | 0
5      | 3      | 0

Why not just store completed tests in UserTests? I don't see a reason to pre-populate it with all tests. In particular, this becomes a maintenance problem if new tests are added or if there are eligibility requirements for certain tests. — Gordon Linoff
– Gordon Linoff, Commented Jul 21, 2014 at 10:48
Well because the user should see a list of all available tests on his/her page and the list should have all completed/non-completed tests. If there is a better way to do just that? I understand there will be a lot of "blank" entries for the non-completed entries. — sd_dracula
– sd_dracula, Commented Jul 21, 2014 at 10:59
. . There is likely to be a better way. You should ask another question with the particular details. — Gordon Linoff
– Gordon Linoff, Commented Jul 21, 2014 at 11:13

mehdi lotfi · Accepted Answer · 2014-07-21 10:38:24Z

1

You can use after trigger for user table.

Create Trigger tr_user on Users 
After Insert 
AS Begin
   INSERT UserTest(USER_ID, TEST_ID, Completed)
   Select I.USER_ID, t.TEST_ID, 0
   From Inserted I, Tests t
END

answered Jul 21, 2014 at 10:38

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sd_dracula Over a year ago

Correct me if I am wrong, but will that not only insert 1 record into UserTests? Instead of one record for each available Test?

mehdi lotfi Over a year ago

for each record that inserted in User table, inserted total rows from tests table with inserted user_id.

Tanner · Accepted Answer · 2014-07-21 11:11:10Z

0

Here's a SQL Fiddle that finds missing records and inserts them.

SQL Fiddle

The SELECT:

select u.user_id, t.test_id, 0 as Completed
from users u 
cross join tests t
where not exists (
        select 1 
        from usertests ut 
        where ut.user_id = u.user_id and ut.test_id = t.test_id)

Adding insert into UserTests (User_Id, Test_Id, Completed) before the select will insert these records.

You can add a user id on to the where clause to do it for a single user if required. It is re-runnable so it won't re-insert test ids for a user that already has them, but will add new ones if new tests are introduced.

answered Jul 21, 2014 at 11:11

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Collectives™ on Stack Overflow

How to insert multiple row values into SQL

2 Answers 2

2 Comments

SQL Fiddle

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related