Loop in SQL 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

General SQL Server Forums

New to SQL Server Programming

Loop in SQL query

Author

Topic

quokwok
Starting Member

1 Post

Posted - 2010-03-04 : 09:38:39

Hi,
I currently have

for (int i = 1; i <= 10; i++)
{
myCommand.CommandText = "INSERT INTO Data_Table (myColumn) VALUES (" + i + ")";
myCommand.ExecuteNonQuery();
}

but this requires many SQL queries. Is there a way to create single SQL query which does the same thing - i.e. a single SQL query with a loop and a variable, so I wouldn't need to have the loop in the programming language?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:40:50

[code]INSERT INTO Data_Table (myColumn)
SELECT number
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND 10
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:42:22

Try this

insert into data_table (mycolumn)
select number from master..spt_values
where type='p' and number between 1 and 10

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:43:02