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"
Go to Top of Page
   

- Advertisement -