July 14, 2009 at 6:24 pm
I am trying to develop a script that will read username in table A and insert them into table B. I would also like the script to to compare the value in table A with table B and if they match then don't insert that user ID. I need some help in writing the code that will do the username check before it's inserted into tableB. Below I have posted what i have so far...all this does it iterate through table A for the usernames to insert into table B no comparison is done, so I will end up with duplicate usernames. Your help is greatly appreciate.
Create Procedure AddNewUser
AS
BEGIN
DECLARE @userids Table
(
ID Int identity(1,1),
UserName varchar(50)
)
Declare @maxid int
Declare @row int
Declare @UserNamevarchar(50)
Declare @inst nvarchar(256)
Declare @user nvarchar(256)
declare @now datetime
Insert into @userids
(UserName)
Select UserName
From TableA
Set @maxid = (select MAX(id) from @userids)
Set @row = 0
While @row < @maxid
Begin
Set @row = @row + 1
Set @userid = (Select UserName from @Userids where id = @row)
set @inst = 'CompanyName' --always the same
set @User = @UserName --User name to insert
set @now = getdate()
exec Set_SecurityProfile --Used to update Table B and others
@PropertyValueString=@inst,@PropertyValueBinary=NULL,@UserName=@User,@IsUserAnonymous=0,@CurrentTimeUtc=@now
End
END
July 14, 2009 at 10:46 pm
First of all, don't Iterate, loop, use Cursors or WHILE loops. I understand that that may seem natural and instinctive, but it's an instinct that will lead you astray. You should always try to find the set-based way to deliver solution in SQL. (For more on this see my articles [/url]on this)
Secondly, here's a better way to do it. It's probably not the best way, but we would need more information for that.
Create Procedure AddNewUser
AS
BEGIN
DECLARE @userids Table
(
ID Int identity(1,1),
UserName varchar(50)
)
Insert into @userids(UserName)
Select UserName
From TableA
Where NOT UserName IN(
Select TargetUsername
from TargetTableName
)
Declare @sql as NVarchar(MAX)
Set = ''
SELECT @sql = @sql + '
EXEC Set_SecurityProfile
@PropertyValueString=''CompanyName''
,@PropertyValueBinary=NULL
,@UserName='''+UserName+'''
,@IsUserAnonymous=0
,@CurrentTimeUtc='''+getdate()+''''
FROM @userids
Print @sql--note: displayed text (including PRINT) gets cut-off
-- somewhere between 256 & 8000 characters.
EXEC @sql
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 16, 2009 at 9:17 am
First, save off a distinct set of users in a temp table, then insert where they do not already exist. This is the set-based approach. No need to use loops or cursors.
select
[user_name]
into#users
fromTableA
group by
[user_name]
insert into dbo.TableB(
[user_name]
)
select
[user_name]
from#users as U
wherenot exists (
select*
fromdbo.TableB as sub
wheresub.[user_name] = U.[user_name]
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply