5t4VFjwD

1CREATE TABLE IF NOT EXISTS users (
2  user_guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
3  first_name VARCHAR(25) NOT NULL,
4  last_name VARCHAR(25) NOT NULL,
5  inserted_at DATETIME NOT NULL CONSTRAINT df__users__inserted_at DEFAULT CURRENT_TIMESTAMP,
6  CONSTRAINT pk__users__user_guid PRIMARY KEY CLUSTERED (user_guid),
7  INDEX ix__users__first_name_last_name NONCLUSTERED (first_name ASC, last_name ASC),
8  INDEX ix__users__insert_at NONCLUSTERED (inserted_at)
9);
10GO
11
12CREATE TABLE IF NOT EXISTS blacklist_users (
13  blacklist_user_guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
14  user_guid UNIQUEIDENTIFIER NOT NULL,
15  inserted_at DATETIME NOT NULL
16    CONSTRAINT df__blacklist_users__inserted_at DEFAULT CURRENT_TIMESTAMP,
17  CONSTRAINT pk__blacklist_users__blacklist_user_guid PRIMARY KEY CLUSTERED (blacklist_user_guid),
18  CONSTRAINT fk__users__user_guid FOREIGN KEY (user_guid) REFERENCES users (user_guid),
19  CONSTRAINT uc__blacklist_users__user_guid UNIQUE (user_guid),
20  INDEX ix__blacklist_users__user_guid NONCLUSTERED (user_guid),
21  INDEX ix__blacklist_users__insert_at NONCLUSTERED (inserted_at)
22);
23GO
24
25-- INSERT INTO users (first_name, last_name)
26-- select_statement
27
28INSERT INTO blacklist_users (user_guid)
29  SELECT
30    user_guid
31  FROM
32    users
33  WHERE
34    last_name = 'Anderson';
35GO
36
37SELECT
38  t_users.*
39FROM
40  users AS t_users
41  INNER JOIN
42    blacklist_users AS t_blacklist_users
43    ON
44      t_users.user_guid != t_blacklist_users.user_guid
45ORDER BY
46  t_users.first_name ASC,
47  t_users.last_name ASC;
48GO