INSERT INTO Users(Name, UserName, role, Password, pyramidPermission, DrugPermission, LoginPermision)
SELECT T.Username, T.UserId, T.UserGroup, T.UserCode, 
T.UserPassString, T.UserPassDrug, T.UserLoginForm FROM (
SELECT Username,UserId, 
CASE
  WHEN ISNULL(UserGroup,2) = -1 THEN 1
  ELSE ISNULL(UserGroup,2) END AS UserGroup,
UserCode = CASE WHEN UserID IN (4,5) THEN UserCode END,
UserPassString, UserPassDrug, UserLoginForm FROM pass 
WHERE UserID <> 100 And ISNULL(UserPassString,'') <> '' AND ISNULL(UserPassDrug,'') <> ''
And UserId NOT IN(SELECT UserName FROM Users)
) AS T 
INNER JOIN 
(
SELECT Userid , MIN(UserName) UserName FROM dbo.Pass

GROUP BY userid
) AS T1 ON T.UserName = T1.UserName AND T.UserID = T1.UserID


