Iterate through Records in stored procedure 2

[spons]

I have a need to Iterate through records in an ms sql stored procedure and insert a record per row. Is this possible? something like..

select Name From users
foreach (user)
{
insert into UserData(test,UserName)values('test',user.Name)
}

Any help would be greatly appreciated, I know I could run this on the clients but I want it run from the stored procedure.

 

[sunila7]

Try this

insert into UserData(test,UserName)
select 'test',[Name] from users


Sunil

 

[SQLSister]

No you don;t want to iterate, forget you ever even heard the word iterate. Bad practice.
You want to use an insert statement with a slect clause instead of a values clause.

insert into UserData(test,UserName)
select 'test', Name From users

this will do all the records in one step and is much faster than iterating through the records.

 

[SQLSister]

Sunila, I see great minds think alike. And yes you are right name should be in brackets as it is a reserved word. Actually spons, you should try to avoid using reserved words as field names.

 

[spons]

Ok Currently my Insert Statement is another procedure that I call using EXEC but its just an insert statement. For future reference is there a way to adjust this method for exec procedureS?

 

[spons]

Ok Before I try this Tell me what you think, Is it ok to just put the variables in the select statement ?

INSERT INTO [ProfitTool].[dbo].[ToolCheckCommissions]([CommissionId],[ToolCheckId],[Amount],[CommissionAmount])
select Commissions.CommissionId,@ToolCheckId,Commissions.CommissionAmount*@EmpFee,Commissions.CommissionAmount

 

[sunila7]

Yes it is allright to use variable names in select stmnt..

The complete SQL shd like as below.... I think u missed the from part in SQL....

INSERT INTO [ProfitTool].[dbo].[ToolCheckCommissions]([CommissionId],[ToolCheckId],[Amount],[CommissionAmount])
select Commissions.CommissionId,@ToolCheckId,Commissions.CommissionAmount*@EmpFee,Commissions.CommissionAmount
From Commissions

Sunil

 

[spons]

Thank you both so much, It works like a charm