Insert Multiple User - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Multiple User

Author

Topic

hisouka
Starting Member

28 Posts

Posted - 2009-04-21 : 01:29:06

Hi i want to create a script that will insert a Multiple User from a table, the username will be. user001 - user500 how can i do that?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 02:00:16

INSERT Table1 (theName)
SELECT 'user' + REPLACE(STR(Number, 3, 0), ' ', '0')
FROM master..spt_values
WHERE type = 'P' And Number between 1 and 500

E 12°55'05.63"
N 56°04'39.26"

hisouka
Starting Member

28 Posts

Posted - 2009-04-21 : 02:08:08

quote:
Originally posted by Peso

INSERT Table1 (theName)
SELECT 'user' + REPLACE(STR(Number, 3, 0), ' ', '0')
FROM master..spt_values
WHERE type = 'P' And Number between 1 and 500

E 12°55'05.63"
N 56°04'39.26"

Hi Peso! Thanks for that.. how about i want to add password where password will be default as '123456' and firsname as fuser001 - 500 and also lastname as luser001 - 500.. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 02:12:30

[code]INSERT Table1
(
firsName,
lastName,
[passWord]
)
SELECT 'fuser' + REPLACE(STR(Number, 3, 0), ' ', '0'),
'luser' + REPLACE(STR(Number, 3, 0), ' ', '0'),
'123456'
FROM master..spt_values
WHERE type = 'P'
And Number between 1 and 500[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-21 : 03:11:39

try this one too
;with cte(i,val)
as
( select 1,
'123456' as val
union all

select i+1,val from cte where i between 1 and 499)
select 'fuser' + RIGHT('00'+CONVERT(VARCHAR(3),i),3),
'luser'+ RIGHT('00'+CONVERT(VARCHAR(3),i),3),
val from cte
OPTION (MAXRECURSION 0)

hisouka
Starting Member

28 Posts

Posted - 2009-04-29 : 04:50:32

quote:

Originally posted by Peso

INSERT	Table1
	(
		firsName,
		lastName,
		[passWord]
	)
SELECT	'fuser' + REPLACE(STR(Number, 3, 0), ' ', '0'),
	'luser' + REPLACE(STR(Number, 3, 0), ' ', '0'),
	'123456'
FROM	master..spt_values
WHERE	type = 'P'
	And Number between 1 and 500

E 12°55'05.63"
N 56°04'39.26"

Hi Peso! It works.. Thanks for that. however it can only generate up to 256 users even though i set the Number between 1 and 500.

Is there any thing that i will do aside from that?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-04-29 : 12:22:50

My spt_values table only has 256 rows where the type is 'P', hence that's all you'll insert based on your where clause. I'm trying to dig up some more information as to what the 'P' value means but being a Microsoft internal table, I haven't found anything of value - YET!

Terry

-- Procrastinate now!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 23:58:53

This is a SQL 2005 forum where number ranges from 0 to 2047.
In SQL 2000, the numbers range from 0 to 255.

E 12°55'05.63"
N 56°04'39.26"

hisouka
Starting Member

28 Posts

Posted - 2009-04-30 : 01:08:59

quote:
Originally posted by Peso

This is a SQL 2005 forum where number ranges from 0 to 2047.
In SQL 2000, the numbers range from 0 to 255.

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso.. That's great..

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-30 : 08:03:03

Peso your answer is excellent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources