Hello to all SQL Masters, I need some help with improvement of below sql query.
what I want to do is get the list of users and put only the changed columns, or new entries into the table which might be simple to achieve, but I don't know if below query is efficient. Basically I am doing something like this at the moment.
Note: I have about 5000 rows and 40 columns to update once an hour, do you have any suggestions to do this in more efficient way?
CREATE PROCEDURE sp_upsertADSusers
@accountExpires[bigint],
@City[nvarchar](max) ,
@co[nvarchar](max) ,
@Company[nvarchar](max) ,
@Created[datetime2](7) ,
@Department[nvarchar](max) ,
@Description[nvarchar](max) ,
@DisplayName[nvarchar](max) ,
@DistinguishedName[nvarchar](max) ,
@Division[nvarchar](max) ,
@Enabled[bit],
@esocontractstart[nvarchar](max) ,
@esodirectorate[nvarchar](max) ,
@esoerpid[nvarchar](max) ,
@esoerpstatus[nvarchar](max) ,
@esogroup[nvarchar](max) ,
@esowikiid[nvarchar](max) ,
@extensionAttribute1[nvarchar](max) ,
@extensionAttribute14[nvarchar](max) ,
@extensionAttribute15[nvarchar](max) ,
@extensionAttribute2[nvarchar](max) ,
@extensionAttribute3[nvarchar](max) ,
@extensionAttribute4[nvarchar](max) ,
@extensionAttribute5[nvarchar](max) ,
@extensionAttribute6[nvarchar](max) ,
@gidNumber[nvarchar](max) ,
@GivenName[nvarchar](max) ,
@HomeDirectory[nvarchar](max) ,
@HomedirRequired[bit],
@HomeDrive[nvarchar](max) ,
@homeMDB[nvarchar](max) ,
@LastBadPasswordAttempt[datetime2](7) ,
@LastLogonDate[datetime2](7) ,
@loginShell[nvarchar](max) ,
@logonCount [int],
@mail[nvarchar](max) ,
@mailNickname[nvarchar](max) ,
@Manager[nvarchar](max) ,
@Modified[datetime2](7) ,
@msExchHomeServerName[nvarchar](max) ,
@msExchWhenMailboxCreated[datetime2](7) ,
@Office[nvarchar](max) ,
@OfficePhone[nvarchar](max) ,
@PasswordExpired[bit],
@PasswordLastSet[datetime2](7) ,
@PasswordNeverExpires[bit],
@PasswordNotRequired[bit],
@PostalCode[nvarchar](max) ,
@preferredLanguage[nvarchar](max) ,
@ProtectedFromAccidentalDeletion[bit],
@proxyAddresses[nvarchar](max) ,
@SamAccountName[nvarchar](max) ,
@SID[nvarchar](max) ,
@State[nvarchar](max) ,
@StreetAddress[nvarchar](max) ,
@Surname[nvarchar](max) ,
@thumbnailPhoto[nvarchar](max) ,
@Title[nvarchar](max) ,
@uid[nvarchar](max) ,
@uidNumber[nvarchar](max) ,
@unixHomeDirectory[nvarchar](max) ,
@UserPrincipalName[nvarchar](max) ,
@whenCreated[datetime2](7) ,
@usercertificate[nvarchar](max) ,
@hascert[bit]
AS
UPDATE [UMT].[dbo].[tblADSusers]
SET [accountExpires]=@accountExpires,
[City]=@City,
[co]=@co,
[Company]=@Company,
[Created]=@Created,
[Department]=@Department,
[Description]=@Description,
[DisplayName]=@DisplayName,
[DistinguishedName]=@DistinguishedName,
[Division]=@Division,
[Enabled]=@Enabled,
[esocontractstart]=@esocontractstart,
[esodirectorate]=@esodirectorate,
[esoerpid]=@esoerpid,
[esoerpstatus]=@esoerpstatus,
[esogroup]=@esogroup,
[esowikiid]=@esowikiid,
[extensionAttribute1]=@extensionAttribute1,
[extensionAttribute14]=@extensionAttribute14,
[extensionAttribute15]=@extensionAttribute15,
[extensionAttribute2]=@extensionAttribute2,
[extensionAttribute3]=@extensionAttribute3,
[extensionAttribute4]=@extensionAttribute4,
[extensionAttribute5]=@extensionAttribute5,
[extensionAttribute6]=@extensionAttribute6,
[gidNumber]=@gidNumber,
[GivenName]=@GivenName,
[HomeDirectory]=@HomeDirectory,
[HomedirRequired]=@HomedirRequired,
[HomeDrive]=@HomeDrive,
[homeMDB]=@homeMDB,
[LastBadPasswordAttempt]=@LastBadPasswordAttempt,
[LastLogonDate]=@LastLogonDate,
[loginShell]=@loginShell,
[logonCount]=@logonCount,
[mail]=@mail,
[mailNickname]=@mailNickname,
[Manager]=@Manager,
[Modified]=@Modified,
[msExchHomeServerName]=@msExchHomeServerName,
[msExchWhenMailboxCreated]=@msExchWhenMailboxCreated,
[Office]=@Office,
[OfficePhone]=@OfficePhone,
[PasswordExpired]=@PasswordExpired,
[PasswordLastSet]=@PasswordLastSet,
[PasswordNeverExpires]=@PasswordNeverExpires,
[PasswordNotRequired]=@PasswordNotRequired,
[PostalCode]=@PostalCode,
[preferredLanguage]=@preferredLanguage,
[ProtectedFromAccidentalDeletion]=@ProtectedFromAccidentalDeletion,
[proxyAddresses]=@proxyAddresses,
[SamAccountName]=@SamAccountName,
[SID]=@SID,
[State]=@State,
[StreetAddress]=@StreetAddress,
[Surname]=@Surname,
[thumbnailPhoto]=@thumbnailPhoto,
[Title]=@Title,
[uid]=@uid,
[uidNumber]=@uidNumber,
[unixHomeDirectory]=@unixHomeDirectory,
[UserPrincipalName]=@UserPrincipalName,
[whenCreated]=@whenCreated,
[userCertificate]=@userCertificate,
[SYNCtimestamp]= getdate()
WHERE[SID] = @SID
AND
[accountExpires]<>@accountExpires or
[City]<>@City or
[co]<>@co or
[Company]<>@Company or
[Created]<>@Created or
[Department]<>@Department or
[Description]<>@Description or
[DisplayName]<>@DisplayName or
[DistinguishedName]<>@DistinguishedName or
[Division]<>@Division or
[Enabled]<>@Enabled or
[esocontractstart]<>@esocontractstart or
[esodirectorate]<>@esodirectorate or
[esoerpid]<>@esoerpid or
[esoerpstatus]<>@esoerpstatus or
[esogroup]<>@esogroup or
[esowikiid]<>@esowikiid or
[extensionAttribute1]<>@extensionAttribute1 or
[extensionAttribute14]<>@extensionAttribute14 or
[extensionAttribute15]<>@extensionAttribute15 or
[extensionAttribute2]<>@extensionAttribute2 or
[extensionAttribute3]<>@extensionAttribute3 or
[extensionAttribute4]<>@extensionAttribute4 or
[extensionAttribute5]<>@extensionAttribute5 or
[extensionAttribute6]<>@extensionAttribute6 or
[gidNumber]<>@gidNumber or
[GivenName]<>@GivenName or
[HomeDirectory]<>@HomeDirectory or
[HomedirRequired]<>@HomedirRequired or
[HomeDrive]<>@HomeDrive or
[homeMDB]<>@homeMDB or
[LastBadPasswordAttempt]<>@LastBadPasswordAttempt or
[LastLogonDate]<>@LastLogonDate or
[loginShell]<>@loginShell or
[logonCount]<>@logonCount or
[mail]<>@mail or
[mailNickname]<>@mailNickname or
[Manager]<>@Manager or
[Modified]<>@Modified or
[msExchHomeServerName]<>@msExchHomeServerName or
[msExchWhenMailboxCreated]<>@msExchWhenMailboxCreated or
[Office]<>@Office or
[OfficePhone]<>@OfficePhone or
[PasswordExpired]<>@PasswordExpired or
[PasswordLastSet]<>@PasswordLastSet or
[PasswordNeverExpires]<>@PasswordNeverExpires or
[PasswordNotRequired]<>@PasswordNotRequired or
[PostalCode]<>@PostalCode or
[preferredLanguage]<>@preferredLanguage or
[ProtectedFromAccidentalDeletion]<>@ProtectedFromAccidentalDeletion or
[proxyAddresses]<>@proxyAddresses or
[SamAccountName]<>@SamAccountName or
[SID]<>@SID or
[State]<>@State or
[StreetAddress]<>@StreetAddress or
[Surname]<>@Surname or
[thumbnailPhoto]<>@thumbnailPhoto or
[Title]<>@Title or
[uid]<>@uid or
[uidNumber]<>@uidNumber or
[unixHomeDirectory]<>@unixHomeDirectory or
[UserPrincipalName]<>@UserPrincipalName or
[whenCreated]<>@whenCreated or
[userCertificate]<>@userCertificate
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [UMT].[dbo].[tblADSusers](
accountExpires,City,co,Company,Created,Department,Description,DisplayName,DistinguishedName,Division,Enabled,esocontractstart,esodirectorate,esoerpid,esoerpstatus,esogroup,esowikiid,extensionAttribute1,extensionAttribute14,extensionAttribute15,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5,extensionAttribute6,gidNumber,GivenName,HomeDirectory,HomedirRequired,HomeDrive,homeMDB,LastBadPasswordAttempt,LastLogonDate,loginShell,logonCount,mail,mailNickname,Manager,Modified,msExchHomeServerName,msExchWhenMailboxCreated,Office,OfficePhone,PasswordExpired,PasswordLastSet,PasswordNeverExpires,PasswordNotRequired,PostalCode,preferredLanguage,ProtectedFromAccidentalDeletion,proxyAddresses,SamAccountName,SID,State,StreetAddress,Surname,thumbnailPhoto,Title,uid,uidNumber,unixHomeDirectory,UserPrincipalName,whenCreated,userCertificate) Select
@accountExpires,
@City,
@co,
@Company,
@Created,
@Department,
@Description,
@DisplayName,
@DistinguishedName,
@Division,
@Enabled,
@esocontractstart,
@esodirectorate,
@esoerpid,
@esoerpstatus,
@esogroup,
@esowikiid,
@extensionAttribute1,
@extensionAttribute14,
@extensionAttribute15,
@extensionAttribute2,
@extensionAttribute3,
@extensionAttribute4,
@extensionAttribute5,
@extensionAttribute6,
@gidNumber,
@GivenName,
@HomeDirectory,
@HomedirRequired,
@HomeDrive,
@homeMDB,
@LastBadPasswordAttempt,
@LastLogonDate,
@loginShell,
@logonCount,
@mail,
@mailNickname,
@Manager,
@Modified,
@msExchHomeServerName,
@msExchWhenMailboxCreated,
@Office,
@OfficePhone,
@PasswordExpired,
@PasswordLastSet,
@PasswordNeverExpires,
@PasswordNotRequired,
@PostalCode,
@preferredLanguage,
@ProtectedFromAccidentalDeletion,
@proxyAddresses,
@SamAccountName,
@SID,
@State,
@StreetAddress,
@Surname,
@thumbnailPhoto,
@Title,
@uid,
@uidNumber,
@unixHomeDirectory,
@UserPrincipalName,
@whenCreated,
@userCertificate
END
GO
