SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 insert 25000 record from user00001 to user25000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

haibec
Yak Posting Veteran

54 Posts

Posted - 04/21/2008 :  03:08:07  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 04/21/2008 :  04:03:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

haibec
Yak Posting Veteran

54 Posts

Posted - 04/21/2008 :  05:16:17  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 04/21/2008 :  05:24:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 04/21/2008 05:27:49
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/21/2008 :  05:28:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 04/21/2008 :  06:04:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000