Update vs Insert from Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Update vs Insert from Query

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-27 : 16:21:48

I have a query that gets active directory name information and then inserts the data into a sql table. This process is no longer since the owner now wants to update the table - not truncate it and rebuild it.

Here is the code:

SET @adsiQuery = N'SELECT userPrincipalName, givenName, cn, ' +
'DisplayName, title, sAMAccountName, distinguishedName ' +
'FROM OPENQUERY (ADSI, ''SELECT userPrincipalName, givenName, sn ' +
'Displayname, title, sAMAccountName, distinguishedName,mail ' +
'FROM ''''LDAP://DC=RL-DC05,DC=jhcohn,DC=com'''' ' +
'WHERE objectClass = ''''User'''' ' +
'and title = ''''*'''' ' +
'AND sn = ''''<alpha>*'''''')'

USE NCOS_Dev

SET @alpha = ASCII('A')
WHILE @alpha <= ASCII('Z')
BEGIN
SET @sql = N'INSERT NCOS_DomainUser ' +
'( DisplayName,sAMAccountName,GivenName,cn,Title,DistinguishedName, ' +
'userPrincipalName,mail ) ' +
REPLACE(@adsiQuery, '<alpha>', CHAR(@alpha))
EXEC(@sql)
SET @alpha = @alpha + 1
END

---------------

So what I want to do is search for the user in the table first and if not found, INSERT. If found update certain fields.

Thanks for all of your help

Bryan Holmstrom

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-27 : 17:44:03

Use MERGE

MERGE ...
USING ...
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED BY TARGET THEN INSERT

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources