i want to insert records like this but...

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

Transact-SQL (2000)

i want to insert records like this but...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2007-11-24 : 03:50:32

for(i=1;i<=40;i++)
{
for(j=1;j<=8;j++)
{
insert into Dept_Content(dept_detail,deptid,subid) values('a'+ j ,i,j)
}
}

i want to insert records like this but it doesnt work, what is wrong ?

please help me out

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-24 : 04:13:31

use this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-24 : 04:19:25

are you using SQL Server 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

asifbhura
Posting Yak Master

165 Posts

Posted - 2007-11-24 : 04:29:41

using 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-24 : 05:19:29

use the function in the link i posted

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 03:59:02

or run this

select 'insert into Dept_Content(dept_detail,deptid,subid) values(''a'+ cast(number as varchar(10))+''',1,'+ cast(number as varchar(10))+')'
from master..spt_values where type='p' and number<41

copy the result and paste it in QA
Run it for 8 times by changing each time the value for deptid

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-28 : 17:34:20

To Build on Madhivanan's example, maybe something like this would work? (also you should get a Numbers table or teh Number function listed by KTan above)

insert into Dept_Content
(dept_detail,deptid,subid)
SELECT 'a' + CAST(JNum AS VARCHAR(10)), JNum, INum
FROM
(
	SELECT Number AS INum
	FROM master.dbo.spt_values
	WHERE type='p' and number <= 8
) AS I
CROSS JOIN
(
	SELECT Number AS JNum
	FROM master.dbo.spt_values
	WHERE type='p' and number <= 40
) AS J

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 01:40:36

quote:
Originally posted by Lamprey

To Build on Madhivanan's example, maybe something like this would work? (also you should get a Numbers table or teh Number function listed by KTan above)
insert into Dept_Content
(dept_detail,deptid,subid)
SELECT 'a' + CAST(JNum AS VARCHAR(10)), JNum, INum
FROM
(
SELECT Number AS INum
FROM master.dbo.spt_values
WHERE type='p' and number <= 8
) AS I
CROSS JOIN
(
SELECT Number AS JNum
FROM master.dbo.spt_values
WHERE type='p' and number <= 40
) AS J

Very nice

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources