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 2000 Forums
 SQL Server Development (2000)
 insert 25000 record from user00001 to user25000

Author  Topic 

haibec
Yak Posting Veteran

54 Posts

Posted - 2008-04-21 : 03:08:07
Hi all!

I want insert 25000 record in to my tabale USER (ID,userid,name) with values userid from user00001 to user25000. Please help me!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 04:03:57
[code]INSERT [User]
(
UserID,
Name
)
SELECT v1.Number + 256 * v2.Number,
'Name ' + CAST(v1.Number + 256 * v2.Number AS VARCHAR(12))
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'
WHERE v1.Type = 'p'
AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

haibec
Yak Posting Veteran

54 Posts

Posted - 2008-04-21 : 05:16:17
quote:
Originally posted by Peso

INSERT		[User]
(
UserID,
Name
)
SELECT v1.Number + 256 * v2.Number,
'Name ' + CAST(v1.Number + 256 * v2.Number AS VARCHAR(12))
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'
WHERE v1.Type = 'p'
AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000



E 12°55'05.25"
N 56°04'39.16"



hi!

I want insert into my table data the values are:

ID userid name
1 userid00001 hello
.
.
25000 userid25000 hello

help me
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 05:24:43
Yes, and I suggested a perfectly valid query for you.
The only three things you have to do is

1) Add ID as column in the INSERT statements. (I didn't do this because you didn't tell us if ID was an IDENTITY column).
2) Change the "name " thing in the SELECT list to "user" and format number with leading zeros.
3) Add a "Hello" in the SELECT list.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 05:28:58
To save some time fot not having to answer more follow-up questions,
INSERT		[User]
(
ID,
UserID,
Name
)
SELECT v1.Number + 256 * v2.Number,
'User' + REPLACE(STR(v1.Number + 256 * v2.Number, 5, 0), ' ', '0'),
'Hello'
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'
WHERE v1.Type = 'p'
AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000
ORDER BY v1.Number + 256 * v2.Number



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

haibec
Yak Posting Veteran

54 Posts

Posted - 2008-04-21 : 06:04:34
quote:
Originally posted by Peso

To save some time fot not having to answer more follow-up questions,
INSERT		[User]
(
ID,
UserID,
Name
)
SELECT v1.Number + 256 * v2.Number,
'User' + REPLACE(STR(v1.Number + 256 * v2.Number, 5, 0), ' ', '0'),
'Hello'
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'
WHERE v1.Type = 'p'
AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000
ORDER BY v1.Number + 256 * v2.Number



E 12°55'05.25"
N 56°04'39.16"


okie thank you very much

Go to Top of Page
   

- Advertisement -