CODEWITHSUNDEEP
sqlsql-server
WilliamB2
WilliamB2

Reputation: 45

Execute SQL statements while looping a table

I want to create a table with a few records in it and then run a set of sql statements for every record in that table. I would use the data in the table to set values in the sql statement.

This should allow me to write the SQL just once and then run it for whatever data I put in the table.

But, I'm not sure how to go about doing this. Should I use a cursor to loop the table? Some other way?

Thanks for any help or advice you can give me.

Upvotes: 0

Views: 249

Answers (3)

Marshall
Marshall

Reputation: 218

CURSOR will have an overhead associated with it, but can be a good method to walk through your table. They are not a totally unnecessary evil and have their place.

With the limited information that WilliamB2 provided, it sounds like a CURSOR set may be a good solution for this problem to walk through his data and generate the multiple downstream INSERTs.

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71908

Maybe you could use INSERT...SELECT instead of the loop:

INSERT INTO target_table
SELECT
    some_col,
    some_other_col,
    'Some fixed value',
    NULL,
    42,
    you_get_the_idea
FROM source_table
WHERE source_table.you_get_the_idea = 1

The columns on your SELECT should match the structure of the target table (you can omit an int/identity pk like id if you have one).

If the best option is this or the loop depends on how many tables you want to populate inside the loop. If it's just a few, I usually stick with INSERT...SELECT.

Upvotes: 0

codingbiz
codingbiz

Reputation: 26376

Yes you can use a cursor. You can also use a while loop

declare @table as table(col1 int, col2 varchar(20))

declare @col1 int
declare @col2 varchar(50)

declare @sql varchar(max)

insert into @table
SELECT col1, col2 FROM OriginalTable

while(exists(select top 1 'x' from @table)) --as long as @table contains records continue
begin
   select top 1 @col1=col1, @col2=col2 from @table

   SET @sql = 'INSERT INTO Table t VALUES('+cast(@col1 as varchar)+')'


   delete top (1) from @table --remove the previously processed row. also ensures no infinite loop
end

I think cursor has an overhead attached to it.

With this second approach you are not working on the original table

Upvotes: 0

Related Questions