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.
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 v1INNER 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" |
|
|
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 v1INNER 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 name1 userid00001 hello..25000 userid25000 hellohelp me |
|
|
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 is1) 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" |
|
|
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 v1INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'WHERE v1.Type = 'p' AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000ORDER BY v1.Number + 256 * v2.Number E 12°55'05.25"N 56°04'39.16" |
|
|
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 v1INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'WHERE v1.Type = 'p' AND v1.Number + 256 * v2.Number BETWEEN 1 AND 25000ORDER BY v1.Number + 256 * v2.Number E 12°55'05.25"N 56°04'39.16"okie thank you very much
|
|
|
|
|
|
|
|