Dummy users bulk insert
Hello,
I am trying to add around 100 users with increasing userid for e.g 001,002...100.
How do I add these in SQL server 2005? Also, there are a few IDs that already exist in the database. Will the script/code stop or just ignore those IDs and move on to add remaining records?
Thank you
I am trying to add around 100 users with increasing userid for e.g 001,002...100.
How do I add these in SQL server 2005? Also, there are a few IDs that already exist in the database. Will the script/code stop or just ignore those IDs and move on to add remaining records?
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
elchuru,
I do not have access to the AD.
cpkilekofp,
Thank you for the code. However, I am trying to understand it before executing since we do not have a test db to try this on!
My questions are-
1. Use master- our 'master' is specified under System Databases and I see all the tables we use are listed under another folder name xxxData. Do I need to modify that line to
use xxxData?
2. Is this code inserting 4 digits IDs?
WHEN @countlen < 4
THEN SUBSTRING('0000', 1, 4 - @countLen) + @countString
ELSE
@countString
I need to insert IDs from guest001 to guest100.
Thank you,
I do not have access to the AD.
cpkilekofp,
Thank you for the code. However, I am trying to understand it before executing since we do not have a test db to try this on!
My questions are-
1. Use master- our 'master' is specified under System Databases and I see all the tables we use are listed under another folder name xxxData. Do I need to modify that line to
use xxxData?
2. Is this code inserting 4 digits IDs?
WHEN @countlen < 4
THEN SUBSTRING('0000', 1, 4 - @countLen) + @countString
ELSE
@countString
I need to insert IDs from guest001 to guest100.
Thank you,
No, You need to be in the master database to execute that code. It does not modify the master database, it just needs to be in the context of the master database. In fact, the only thing in that code you should modify is the parts I mentioned earlier. I modified the number of logins for my test to 3 (yes, this code has been thoroughly tested). Try it with three users yourself first, that will establish it works and if you don't like the results you can delete the users and logins easily enough (that is how I cleaned up after my test).
If you want to generate 3-digit ids, then change that block of code to read:
WHEN @countlen < 3
THEN SUBSTRING('0000', 1, 3 - @countLen) + @countString
ELSE
@countString
If you want to generate 3-digit ids, then change that block of code to read:
WHEN @countlen < 3
THEN SUBSTRING('0000', 1, 3 - @countLen) + @countString
ELSE
@countString
If you have AD that method will save you a lot of time and trouble in the future as you won't have to manage the 100 logins.