Steven S. Ross

Ok ignore the title, SQL is all business so lets get back to the “serious” SQL train.

So maybe you have a table that is very flat, someone did something like data1, data2, data3 as column names and you want to loop over it and insert it into a new table that can hold an infinite amount of meta-data with a relationship. Hey after all we are programming with relational databases these days.

So here are the basics, the loop (from 1 to 10):

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <11)
BEGIN
-- DO LOOP STUFF HERE
SET @intFlag = @intFlag + 1
CONTINUE;
END
GO

So that is useful and all but, how do we create some dynamic sql with lovely concatenations?

Like so:

Declare @SQL VarChar(1000)
Select @SQL = 'INSERT new_table_name(description, status, date_due, date_complete, date_start, ref_id)'
Select @SQL = @SQL + ' SELECT descrip' + CAST(@intFlag AS varchar) + ', status' + CAST(@intFlag AS varchar) + ', due' + CAST(@intFlag AS varchar) + ', complete' + CAST(@intFlag AS varchar) + ', start' + CAST(@intFlag AS varchar) + ', old_id'
Select @SQL = @SQL + ' FROM old_table_name'
Select @SQL = @SQL + ' WHERE required_column' + CAST(@intFlag AS varchar) + ' IS NOT NULL'
Exec ( @SQL)

So now we just combine it all:

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <11)
BEGIN
Declare @SQL VarChar(1000)
Select @SQL = 'INSERT new_table_name(description, status, date_due, date_complete, date_start, ref_id)'
Select @SQL = @SQL + ' SELECT descrip' + CAST(@intFlag AS varchar) + ', status' + CAST(@intFlag AS varchar) + ', due' + CAST(@intFlag AS varchar) + ', complete' + CAST(@intFlag AS varchar) + ', start' + CAST(@intFlag AS varchar) + ', old_id'
Select @SQL = @SQL + ' FROM old_table_name'
Select @SQL = @SQL + ' WHERE required_column' + CAST(@intFlag AS varchar) + ' IS NOT NULL'
Exec ( @SQL)
SET @intFlag = @intFlag + 1
CONTINUE;
END
GO

Ask away if there are any questions.