Help required in query - 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)

Help required in query

Author

Topic

vishalmthakkar
Starting Member

2 Posts

Posted - 2010-10-01 : 23:58:33

Hi,

I want query which will insert records in given table with following condition.

If 1 member take 100 id then i need to insert 100 records in the table
like this.
insert('Plan1',1)
insert('Plan2',1)
insert('Plan3',1)
insert('Plan4',1)
|
|
insert('Plan100',1)

I dont want to user loop for inserting 100 records.

Table structure is
Plan(Id,PlanCode,Memberid)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-02 : 02:39:00

Try this:

--Sample table
Create table [Plan]
(Id int identity,
PlanCode Varchar(50),
Memberid int)

--Inserting data without looping.

Declare @Memberid int --To hold member id
Set @MemberID = 1

Insert into [Plan] (PlanCode,MemberID)
Select 'Plan'+ (Select Cast(isnull(count(*),0) +1 as varchar(10)) from [Plan] SubTab
where SubTab.MemberID = @MemberID ) , @MemberID
Go 100 --Reccount

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Sachin.Nand

2937 Posts

Posted - 2010-10-02 : 03:34:29

quote:
Originally posted by pk_bohra

Try this:

--Sample table
Create table [Plan]
(Id int identity,
PlanCode Varchar(50),
Memberid int)

GO
--Inserting data without looping.

Declare @Memberid int --To hold member id
Set @MemberID = 1

Insert into [Plan] (PlanCode,MemberID)
Select 'Plan'+ (Select Cast(isnull(count(*),0) +1 as varchar(10)) from [Plan] SubTab
where SubTab.MemberID = @MemberID ) , @MemberID
Go 100 --Reccount

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Little correction of bohra's query marked in red above.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-10-02 : 03:41:21

But isn't GO 100 some kind of loop which is much horrible than a normal loop?

another way


Insert into [Plan] (PlanCode,MemberID)
Select 'Plan'+ Cast(number as varchar(10)),1 from master.dbo.spt_values where type='p'
and number between 1 and 100

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources