CODEWITHSUNDEEP
sqlsql-server
sd_dracula
sd_dracula

Reputation: 3896

How to insert multiple row values into SQL

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

Upvotes: 0

Views: 90

Answers (2)

Tanner
Tanner

Reputation: 22733

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.

Upvotes: 0

mehdi lotfi
mehdi lotfi

Reputation: 11571

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

Upvotes: 1

Related Questions