January 26, 2012 at 10:53 am
I have been struggling with finding an efficient solution to updating large blocks of Users.
We have customers that provide us with name, address, phone and email information for all of their employees in large, external files. In most cases, customers will send me a file and tell us that this is our current employee list.
So, I need to merge their new upload file (probably 99% of the file hasn't changed from the previous load) with their current production user base.
In our database, User information is split between two tables: Users and UserAuthentication.
The User_Id value is created by an INSERT into the UserAuthentication table and that User_Id is a foreign key in the Users table.
Since the data provided in a customer upload file is basically the data contained in a Users record, I'm attempting to construct a process using a MERGE operation against the Users data table. I have the update logic figured out as it is fairly straight-forward. The problem comes when I have a new user present in the upload file and it doesn't match on the MERGE expression. Before I can INSERT a new Users record, I have to first INSERT a new UserAuthentication table record, and capture its User_Id value to plug into the INSERT for a Users record.
Question: Using a MERGE operation against Users table, when records don't match and need to be inserted, how can I first cause the UserAuthetication record to be created in such a manner that its User_Id value is captured for use in creating a new Users record?
Here is the T-SQL code to create the base tables:
CREATE TABLE dbo.LK_Users (
USER_IDINTNOT NULL,
FirstNameNVARCHAR(100) NOT NULL,
LastNameNVARCHAR(100) NOT NULL,
Phone1NVARCHAR(20) NULL,
EmailNVARCHAR(100) NULL
); -- DROP TABLE dbo.LK_Users
CREATE TABLE dbo.LK_UserAuth (
[User_Id]INT IDENTITY(1,1)NOT NULL,
[Org_id]INTNOT NULL,
[Username]NVARCHAR(50)NULL,
[Password]NVARCHAR(50)NULL
); -- DROP TABLE dbo.LK_UserAuth
CREATE TABLE dbo.LK_staged_Users (
FirstNameNVARCHAR(100) NOT NULL,
LastNameNVARCHAR(100) NOT NULL,
Phone1NVARCHAR(20) NULL,
EmailNVARCHAR(100) NULL
); -- DROP TABLE dbo.LK_staged_users
INSERT INTO dbo.lk_staged_users (
FirstName, LastName, Phone1, Email )
SELECT 'Larry', 'Kruse', '2817989059', 'NEW_lkruse@techradium.com'
UNION ALL
SELECT 'Jermaine', 'Gonzales', '2819991212', 'NEW_jgonzales@techradium.com'
UNION ALL
SELECT 'David', 'Hickey', '2812636311', 'NEW_dhickey@techradium.com'
INSERT INTO dbo.LK_UserAuth ( [Org_Id], [Username], [Password] )
SELECT 8, 'lkruse', 'password1'
UNION ALL
SELECT 8, 'jgonzales', 'password2'
INSERT INTO dbo.LK_Users ( user_Id, FirstName, LastName, Phone1, Email )
SELECT 1, 'Larry', 'Kruse', '2812636328', 'lkruse@swbell.net'
UNION ALL
SELECT 2, 'Jermaine', 'Gonzales', '2812636322', 'jermaineg@techradium.com'
SELECT * FROM LK_UserAuth
SELECT * FROM LK_Users
SELECT * FROM LK_staged_users
The above SELECT statements will show the 3 tables involved:
UserAuthentication - this is the table that contains the IDENTITY(1,1) field creation.
Users - this is the table that contains the bulk of the data to be MERGEd.
staged_users - this is the table which contains the newly imported data without the User_Id field.
I next create a TABLE variable named @myTempUsers which contains a User_Id field initialized to NULL. (The reason I am doing this is to be able to generate a derived table with identical fields to the Users table so that I can perform a MERGE statement.)
Here is the code which creates the table variable:
DECLARE @myTempUsers TABLE (
[User_Id]INTNULL,
FirstNameNVARCHAR(100)NULL,
LastNameNVARCHAR(100)NULL,
Phone1NVARCHAR(20)NULL,
EmailNVARCHAR(100)NULL
);
INSERT INTO @myTempUsers ( User_Id, FirstName, LastName, Phone1, Email )
SELECT
NULL,
FirstName, LastName, Phone1, Email
FROM dbo.LK_staged_users
SELECT * FROM @myTempUsers
And here is my initial cut at trying to get the MERGE statement to work. Obviously, if you try to run it, you will receive the same error I get in that I am not able to create a record in the Users table without having a non-NULL value in the User-Id field. And in order to plug that in, I will need to first generate the UserAuthentication record and capture its User_Id value.
MERGE INTO dbo.LK_Users AS prod
USING @myTempUsers AS stg -- ( [User_Id], FirstName, LastName, Phone1, Email )
ON ( stg.FirstName= prod.FirstName
AND stg.LastName= prod.LastName )
WHEN MATCHED
THENUPDATE
SET Phone1 = stg.Phone1,
Email= stg.Email
WHEN NOT MATCHED
THENINSERT ( [User_Id], FirstName, LastName, Phone1, Email )
VALUES ( [User_Id], FirstName, LastName, Phone1, Email )
OUTPUT $ACTION, INSERTED.*;
I've scoured all over BOL and the forumns here, but still haven't found anything that works.
Can anyone suggest correct syntax for the MERGE or suggest a better way to handle this situation?
Any advice is greatly appreciated folks! Thanks!
Larry Kruse
January 26, 2012 at 11:04 am
The only way I can think of to divert an insert from one place to another would be to do the Merge command on a View that queries both tables, and which has an Instead of Update, Insert, Delete trigger on it, which would perform the correct actions on each command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2012 at 12:14 pm
Thanks for the response G.
uh-oh...if you are out of answers on how to do this,then I'm hosed...
I was thinking that I might be able to get away with some kind of pretty elaborate CTE expression somehow. I think what it seems like I really need is a syntax that allows a nested INSERT something like:
INSERT INTO dbo.LK_Users ( [User_Id], [FirstName], [LastName], [Phone1], [Email] )
VALUES ( (INSERT INTO dbo.LK_UserAuth OUTPUT INSERTED.User_Id),
@FirstName,
@LastName,
@Phone1,
)
January 26, 2012 at 12:25 pm
Honestly, you're going to have to go with two (or more steps). Do a Merge into the UsersAuth table using a join between your staging table and the Users table, grab the Output from that to get the generated ID values and join that to the staging table to Merge into Users. That's the shortest, easiest, simplest, et al, solution. It's what I'd do.
I just can't think of a way to do it one step, because it really doesn't work that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 7:04 am
Hmmm...
I began working on your first suggested solution G, and I created a view against the LK_Users and LK_UserAuth tables.
But when I considered how I had to address the creation of the LK_UserAuth record before inserting a new Users record I figured out a possible solution using an "INSTEAD OF INSERT" trigger on the LK_Users_v. The trigger seems to work fine in a case where only one record is being inserted, but it fails when processing a record-set with more than 1 result in it.
I tried like heck to figure out the syntax for using the OUTPUT clause on the INSERT into the LK_UserAuth table but I finally fell back to using a "SCOPE_IDENTITY()" to retrieve the identity value for the LK_Users insert, however it plugged in the exact same User_Id identity value for all 3 records of a test insert.
I have something wrong with this INSTEAD OF INSERT trigger, and I was wondering if you might be able to point me back to the right direction please?
Here is the simple view:
CREATE VIEW dbo.LK_Users_v AS
SELECT ua.[User_Id],
ua.[Org_Id],
ua.[Username],
ua.[Password],
u.[FirstName],
u.[LastName],
u.[Phone1],
u.[Email]
FROM dbo.LK_Users AS u
INNER JOIN dbo.LK_UserAuth AS ua
ON u.USER_ID = ua.User_Id
GO
Here is the code for the INSTEAD OF INSERT trigger definition (which I believe is in error):
CREATE TRIGGER trg_INSERT_Users_v ON dbo.LK_Users_v
INSTEAD OF INSERT
AS
BEGIN
--DECLARE @myIDsTABLE ( [new_Id] INT )
DECLARE @newIDINT = NULL;
INSERT dbo.LK_UserAuth ( Org_id, Username, [Password] )
--OUTPUTINSERTED.[User_Id] INTO @myID ( new_Id )
SELECTOrg_Id,
Username,
[Password]
FROMINSERTED
SET @newID = SCOPE_IDENTITY();
INSERT dbo.LK_Users ( USER_ID, FirstName, LastName, Phone1, Email )
SELECT @newID,
FirstName,
LastName,
Phone1,
FROMINSERTED
END
GO
In order to utilize this update to a view in MERGE statement, I also had to create an INSTEAD OF UPDATE trigger on the view too. So I then added the following code:
CREATE TRIGGER trg_UPDATE_Users_v ON dbo.LK_Users_v
INSTEAD OF UPDATE
AS
BEGIN
UPDATE u
SET u.[Username]= i.[Username],
u.[Password]= i.[Password]
FROM dbo.LK_UserAuth AS u
INNER JOIN INSERTED AS i
ON i.[User_id] = u.[User_Id]
UPDATE u
SET Firstname= i.FirstName,
LastName= i.LastName,
Phone1= i.Phone1,
Email= i.Email
FROM dbo.LK_Users AS u
INNER JOIN INSERTED AS i
ON i.[User_Id] = u.[User_id]
END
GO
To test out the triggers, I then executed an INSERT against the LK_Users_v using the following T-SQL code.
INSERT INTO dbo.LK_Users_v
( User_Id ,
Org_Id ,
Username ,
Password ,
FirstName ,
LastName ,
Phone1 ,
)
SELECT 0 , -- User_Id - int
8 , -- Org_Id - int
N'TestingOne' , -- Username - nvarchar(50)
N'pass1' , -- Password - nvarchar(50)
N'Testing' , -- FirstName - nvarchar(100)
N'One' , -- LastName - nvarchar(100)
N'7175551212' , -- Phone1 - nvarchar(20)
N'tone@gmail.com' -- Email - nvarchar(100)
UNION ALL
SELECT 0 , -- User_Id - int
8 , -- Org_Id - int
N'TestingTwo' , -- Username - nvarchar(50)
N'pass2' , -- Password - nvarchar(50)
N'Testing' , -- FirstName - nvarchar(100)
N'Two' , -- LastName - nvarchar(100)
N'7175551212' , -- Phone1 - nvarchar(20)
N'ttwo@gmail.com' -- Email - nvarchar(100)
UNION ALL
SELECT 0 , -- User_Id - int
8 , -- Org_Id - int
N'TestingThree' , -- Username - nvarchar(50)
N'pass3' , -- Password - nvarchar(50)
N'Testing' , -- FirstName - nvarchar(100)
N'Three' , -- LastName - nvarchar(100)
N'7175551212' , -- Phone1 - nvarchar(20)
N'tThree@gmail.com' -- Email - nvarchar(100)
The end result is that all 3 records were added, however all 3 of the above inserts contained the same value for User_Id.
I'm pretty certain that the problem lies in the INSTEAD OF INSERT trigger on the view, but I can't seem to figure out a way to keep the User_Id values inserted into LK_Users in sync with the inserts into the LK_UserAuth table.
Do you have any advice? Thanks!
Larry
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply