I have a many to many table with one staff to many clients. Based on a new requirement I have to now add many clients to many staff.
My table has over 1000 rows, and my question is how do I write a T-SQL statement that will migrate the data correctly from the client table and insert into my junction table called StaffClient?
Here is my current Client table where ClientID is the primary key and StaffID is the foreign key. One staff member has many clients
ClientId StaffId
----------------------------------------------------------------------------
F7DD6029-27AF-A9ED-57A2-FE25B815BFA7 3AA1C003-F0F1-49AB-84A3-26E747EE4581
1CB8FE0F-2D21-4E5B-99E9-FBBDF0EEBDC7 3AA1C003-F0F1-49AB-84A3-26E747EE4581
D87659C2-7916-4048-A165-F4BB0B4937D3 NULL
E18E3C8D-92DB-4627-BB73-F43CB67A3AEE NULL
50795DB2-085E-46FD-97DE-F33B6AE39EE0 NULL
45FE2A69-583F-CBF7-750A-E9C93FC52D27 NULL
86B13CB5-41DB-4016-8D3D-E467B0CD6EE9 NULL
24508726-61CE-4C27-B6D7-DBABBAAD141B 8E3990F4-68B7-4F0F-AD40-923ECAC7A476
66FDFC91-1E27-4AEE-A8F6-D79B54CF601D NULL
A99A0D77-612F-47B7-A82B-D0E51ADF1E49 NULL
59388388-5AEC-4AC5-BD54-CF802734AAD1 B7B0897A-8D40-408E-841B-598C3C425762
79747776-5867-458F-8FD5-CDD299AA3595 NULL
F5A8EFF1-3996-424C-9EF8-CD60D0B900DE 3AA1C003-F0F1-49AB-84A3-26E747EE4581
My question is how do I write a SQL statement for over 1000 rows that will now go to my junction table called StaffClient.
Here is the SQL for my junction table
CREATE TABLE StaffClient
(
StaffId uniqueidentifier,
ClientId uniqueidentifier
PRIMARY KEY (StaffId, ClientId),
FOREIGN KEY (StaffId) REFERENCES Staff(StaffId),
FOREIGN KEY (ClientId) REFERENCES Client(ClientId)
);
Run this select statement to ensure that the correct data is returned:
SELECT ClientId, StaffId
FROM Client
WHERE StaffId IS NOT NULL;
Once you've verified, run this:
INSERT INTO StaffClient (ClientId, StaffId)
SELECT ClientId, StaffId
FROM Client
WHERE StaffId IS NOT NULL;