I was considering if ChatGPT could help me write SQL code to merge a SharePoint Delta State Dataset, like the ones we are now offering using the Microsoft Graph Data Connect (MGDC). If you’re new to the scenario, you can read about it at this blog post: SharePoint on MGDC FAQ: How can I use Delta State Datasets?
Here is how it went…
Jose Barreto:
I have a SQL server table with a list of users. The table includes a user id, a user name and a user age.
ChatGPT:
To create this table in SQL Server, you could use the following SQL command:
CREATE TABLE Users (
UserID INT PRIMARY KEY IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserAge INT NOT NULL
);
This creates a table called “Users” with three columns: UserID, UserName, and UserAge. The UserID column is an auto-incrementing primary key, UserName is a non-nullable string with a maximum length of 50 characters, and UserAge is a non-nullable integer.
Jose Barreto:
Now I have a second table with changes to the user table. That second table includes a user id, a user name, a user age and an operation, which could be inserted, updated or deleted.
ChatGPT:
To create the second table in SQL Server, you could use the following SQL command:
CREATE TABLE UserChanges (
UserID INT NOT NULL,
UserName VARCHAR(50) NOT NULL,
UserAge INT NOT NULL,
Operation VARCHAR(10) NOT NULL
);
This creates a table called “UserChanges” with four columns: UserID, UserName, UserAge, and Operation. UserID is a non-nullable integer, UserName is a non-nullable string with a maximum length of 50 characters, UserAge is a non-nullable integer, and Operation is a non-nullable string with a maximum length of 10 characters. This table can be used to keep track of changes to the original Users table, such as inserts, updates, and deletes.
Jose Barreto:
Write some SQL to update the contents of the Users table with the data coming from the UserChanges table. If the operation is ‘Inserted’, you should add a new user. If the operation is ‘Updated’, you should update an existing user. If the operation is ‘Deleted’, you should delete theuser.
ChatGPT:
To update the contents of the Users table with the data from the UserChanges table, you could use the following SQL code:
BEGIN TRANSACTION;
DECLARE @UserID INT, @UserName VARCHAR(50), @UserAge INT, @Operation VARCHAR(10);
DECLARE UserChanges_Cursor CURSOR FOR
SELECT UserID, UserName, UserAge, Operation
FROM UserChanges;
OPEN UserChanges_Cursor;
FETCH NEXT FROM UserChanges_Cursor INTO @UserID, @UserName, @UserAge, @Operation;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Operation = 'Inserted'
BEGIN
INSERT INTO Users (UserID, UserName, UserAge)
VALUES (@UserID, @UserName, @UserAge);
END
ELSE IF @Operation = 'Updated'
BEGIN
UPDATE Users
SET UserName = @UserName, UserAge = @UserAge
WHERE UserID = @UserID
END
ELSE IF @Operation = 'Deleted'
BEGIN
DELETE FROM Users
WHERE UserID = @UserID;
END
FETCH NEXT FROM UserChanges_Cursor INTO @UserID, @UserName, @UserAge, @Operation;
END;
CLOSE UserChanges_Cursor;
DEALLOCATE UserChanges_Cursor;
COMMIT TRANSACTION;
This code uses a cursor to iterate through the records in the UserChanges table. For each record, it checks the value of the Operation column and performs an insert, update, or delete on the Users table as appropriate. The transaction is used to ensure that all changes are committed together, or if an error occurs, that none of the changes are committed.
Jose Barreto:
Could this be done without using a cursor?
ChatGPT:
Yes, this can be done without using a cursor in SQL Server. You can use a case statement within a single UPDATE or MERGE statement to update the Users table based on the Operation column in the UserChanges table. Here’s an example:
BEGIN TRANSACTION;
MERGE INTO Users AS Target
USING (
SELECT UserID, UserName, UserAge, Operation
FROM UserChanges
) AS Source
ON Target.UserID = Source.UserID
WHEN MATCHED AND Source.Operation = 'Updated' THEN
UPDATE SET Target.UserName = Source.UserName, Target.UserAge = Source.UserAge
WHEN NOT MATCHED BY TARGET AND Source.Operation = 'Inserted' THEN
INSERT (UserID, UserName, UserAge)
VALUES (Source.UserID, Source.UserName, Source.UserAge)
WHEN MATCHED AND Source.Operation = 'Deleted' THEN
DELETE;
COMMIT TRANSACTION;
This code uses the MERGE statement to perform an update, insert, or delete in a single statement, based on whether a match is found and the value of the Operation column. The transaction is used to ensure that all changes are committed together, or if an error occurs, that none of the changes are committed.
Jose Barreto:
Can this be done without a cursor and without using the merge command?
ChatGPT:
Yes, you can do this without using a cursor or the MERGE statement in SQL Server by using a combination of UPDATE, INSERT, and DELETE statements with CASE expressions. Here’s an example:
BEGIN TRANSACTION;
UPDATE Users
SET UserName = CASE WHEN Operation = 'Updated' THEN UserChanges.UserName ELSE UserName END,
UserAge = CASE WHEN Operation = 'Updated' THEN UserChanges.UserAge ELSE UserAge END
FROM Users
JOIN UserChanges
ON Users.UserID = UserChanges.UserID
AND UserChanges.Operation = 'Updated';
INSERT INTO Users (UserID, UserName, UserAge)
SELECT UserID, UserName, UserAge<br>FROM UserChanges
WHERE Operation = 'Inserted';
DELETE FROM Users
JOIN UserChanges
ON Users.UserID = UserChanges.UserID
AND UserChanges.Operation = 'Deleted';
COMMIT TRANSACTION;
This code uses separate UPDATE, INSERT, and DELETE statements with CASE expressions to update, insert, or delete records in the Users table based on the values in the UserChanges table. The transaction is used to ensure that all changes are committed together, or if an error occurs, that none of the changes are committed.
P.S.: After publishing this blog about ChatGPT, I got access to the New Bing with Chat option. Here’s how it responded to the same question (I posted everything in one prompt).
Jose Barreto:
I have a SQL server table with a list of users. The table includes a user id, a user name and a user age. Now I have a second table with changes to the user table. That second table includes a user id, a user name, a user age and an operation, which could be inserted, updated or deleted. Write some SQL to update the contents of the Users table with the data coming from the UserChanges table. If the operation is ‘Inserted’, you should add a new user. If the operation is ‘Updated’, you should update an existing user. If the operation is ‘Deleted’, you should delete the user.
Bing Chat:
Hello, this is Bing. I can help you with some SQL code. Here is one possible solution:
-- Assuming the Users table has a primary key constraint on user_id
-- and the UserChanges table has a foreign key constraint on user_id referencing Users
-- First, delete the users that have operation = 'Deleted' in UserChanges
DELETE FROM Users
WHERE user_id IN (SELECT user_id FROM UserChanges WHERE operation = 'Deleted');
-- Next, update the users that have operation = 'Updated' in UserChanges
UPDATE Users
SET user_name = UC.user_name,
user_age = UC.user_age
FROM Users U
JOIN UserChanges UC ON U.user_id = UC.user_id
WHERE UC.operation = 'Updated';
-- Finally, insert the users that have operation = 'Inserted' in UserChanges
INSERT INTO Users (user_id, user_name, user_age)
SELECT user_id, user_name, user_age FROM UserChanges WHERE operation = 'Inserted';
Jose Barreto:
Can you make this work as a transaction?
Bing Chat:
Sure, I can make this work as a transaction. Here is the modified code:
-- Start a transaction
BEGIN TRANSACTION;
-- Perform the same steps as before
DELETE FROM Users
WHERE user_id IN (SELECT user_id FROM UserChanges WHERE operation = 'Deleted');
UPDATE Users
SET user_name = UC.user_name,
user_age = UC.user_age
FROM Users U
JOIN UserChanges UC ON U.user_id = UC.user_id
WHERE UC.operation = 'Updated';
INSERT INTO Users (user_id, user_name, user_age)
SELECT user_id, user_name, user_age FROM UserChanges WHERE operation = 'Inserted';
-- Commit the transaction
COMMIT TRANSACTION;
My final take:
Bing Chat got a similar response, got what I wanted in the first try and I liked its style better (subquery for the delete portion and comments inside the code). I did have to prompt it to make it a transaction, but it had no problem adjusting.
Color me impressed…
Jose Barreto's Blog 