Insert 5000 user in my DB ! - 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

Old Forums

CLOSED - General SQL Server

Insert 5000 user in my DB !

Author

Topic

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-04 : 11:51:10

Hi...!

I have table User includes fields : username, password,name. I want insert concurent 5000 user have username from user1 to user5000 and password is values random . Please help me .!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 12:23:57

This is one way to go.

select		'User' + convert(varchar, e.number) username,
		replace(newid(), '-', '') pass
from		F_TABLE_NUMBER_RANGE(1, 5000) e
order by	e.number

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-09-04 : 12:35:22

Hi Haibec

I have wriiten this off heart try and ammend it

CREATE PROCEDURE [dbo].[GenerateUsers]

@username as varchar(50),
@name as varchar(50),
@ReqRMANumber as int

AS

DECLARE @COUNTER INT
DECLARE @Password varchar(66)
SET @Password = 'myPassword'
SET @Name = 'my\NN\Name '

SET @COUNTER = 1

WHILE (@COUNTER) <= @ReqRMANumber

Set @Username = @Username +CONVERT(VARCHAR(50),@COUNTER)

BEGIN
insert into USERS (username, password, name) values (@username , @password, @name)
SET @COUNTER = @COUNTER + 1
END

GO

If it is that easy, everybody will be doing it

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-04 : 23:16:28

quote:
Originally posted by Peso

This is one way to go.
select 'User' + convert(varchar, e.number) username,
replace(newid(), '-', '') pass
from F_TABLE_NUMBER_RANGE(1, 5000) e
order by e.number

Peter Larsson
Helsingborg, Sweden

Not run !huhuhu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-05 : 00:20:46

quote:
Originally posted by haibec
...Not run !huhuhu...

WTF?

CODO ERGO SUM

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-05 : 00:46:54

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by haibec
...Not run !huhuhu...

WTF?
I don't Understand

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 00:55:16

You have to copy the function F_TABLE_NUMBER_RANGE from here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685[/url] and run first. Then you can run the code above.

Peter Larsson
Helsingborg, Sweden

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-05 : 00:55:54

quote:
Originally posted by OBINNA_EKE

Hi Haibec

I have wriiten this off heart try and ammend it

CREATE PROCEDURE [dbo].[GenerateUsers]

@username as varchar(50),
@name as varchar(50),
@ReqRMANumber as int

AS

DECLARE @COUNTER INT
DECLARE @Password varchar(66)
SET @Password = 'myPassword'
SET @Name = 'my\NN\Name '

SET @COUNTER = 1

WHILE (@COUNTER) <= @ReqRMANumber

Set @Username = @Username +CONVERT(VARCHAR(50),@COUNTER)

BEGIN
insert into USERS (username, password, name) values (@username , @password, @name)
SET @COUNTER = @COUNTER + 1
END

GO

If it is that easy, everybody will be doing it

I want password must defferent and en to end code by MD5 . Please help me!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-05 : 01:28:54

Just google for it and there it is
MD5 Hash SQL Server Extended Stored Procedure http://www.codeproject.com/database/xp_md5.asp

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-05 : 03:33:37

quote:
Originally posted by Peso

You have to copy the function F_TABLE_NUMBER_RANGE from here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685[/url] and run first. Then you can run the code above.

Peter Larsson
Helsingborg, Sweden

Hi you!
I run it and very well but it not save in to my table. Please tell me about this problem

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-05 : 03:42:32

quote:
Originally posted by haibec

quote:
Originally posted by Peso

You have to copy the function F_TABLE_NUMBER_RANGE from here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685[/url] and run first. Then you can run the code above.

Peter Larsson
Helsingborg, Sweden

Hi you!
I run it and very well but it not save in to my table. Please tell me about this problem

Peter's Query is just for selection.. for inserting you need to do like this ..


Insert [User] ([UserName],[Password])
select		'User' + convert(varchar, e.number) username,
		replace(newid(), '-', '') pass
from		F_TABLE_NUMBER_RANGE(1, 5000) e
order by	e.number

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 04:07:17

This seems a little bit risky with username and password generation, given that he doesn't know INSERTs.

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-09-05 : 06:37:48

My Guy cut & paste this on query analyser, Post back if you need more help

CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

Insert INTO [Users] ([UserName],[Password])
select 'User' + convert(varchar, e.number) username,
replace(newid(), '-', '') pass
from xf_TableNumberRange(1, 5) e
order by e.number

select * from users

If it is that easy, everybody will be doing it

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-05 : 07:58:38

quote:
Originally posted by OBINNA_EKE

My Guy cut & paste this on query analyser, Post back if you need more help

CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

Insert INTO [Users] ([UserName],[Password])
select 'User' + convert(varchar, e.number) username,
replace(newid(), '-', '') pass
from xf_TableNumberRange(1, 5) e
order by e.number

select * from users

If it is that easy, everybody will be doing it

Its better if you this function for generating the numbers based on rages.. its very fast... and without looping..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=Numbers,table

Chirag

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-05 : 11:03:25

quote:
Originally posted by OBINNA_EKE

My Guy cut & paste this on query analyser, Post back if you need more help

CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

Insert INTO [Users] ([UserName],[Password])
select 'User' + convert(varchar, e.number) username,
replace(newid(), '-', '') pass
from xf_TableNumberRange(1, 5) e
order by e.number

select * from users

If it is that easy, everybody will be doing it

Okieeeeeee! It is very well !Thank a lot !

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-05 : 11:12:07

quote:
Originally posted by OBINNA_EKE

My Guy cut & paste this on query analyser, Post back if you need more help

CREATE FUNCTION xf_TableNumberRange(@nMin INT, @nMax INT)
RETURNS @t TABLE (number INT) AS
BEGIN
DECLARE @i INT
SET @i = @nMin
WHILE @i <= @nMax
BEGIN
INSERT INTO @t (number) VALUES (@i)
SELECT @i = @i + 1
END
RETURN
END

Insert INTO [Users] ([UserName],[Password])
select 'User' + convert(varchar, e.number) username,
replace(newid(), '-', '') pass
from xf_TableNumberRange(1, 5) e
order by e.number

select * from users

If it is that easy, everybody will be doing it

hi!But i want how to know their password !hic..hic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 11:25:44

select * from [users] where username = 'user123'

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-05 : 13:45:08

my guess is that he wants to have
a username, a MD5 hashed password and non-hashed value of the password.

for this you have to create a function to hash the password so you can call it for each row.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources