About myself and everyone else.
I can’t remember how many times I’ve written loops in order to build column lists for inserts.
The first thing I would do is determine the number of columns and the loop for that number of times. This was done in order to put the proper brackets (By the way this is a SQL post) , you need the correct brackets in order to do the insert statement
insert into blah(colum 1, column2 )
So to dynamically determine the insert and build it with dynamic SQL you have to use a loop.
Then I recently (1337 DBA fail -1) found out you can just use the normal iterative’ness of a select to achieve this.
Declare @columnList varchar(1000)
Set @columnlist = ”
select @columnlist = @columnlist +‘[‘+ name +‘],’fromsys.columns
So you can achieve it with the above. You will need to do a replace on the ‘],’ with a single bracket and that should do it.
You have to set the initial value to ” otherwise it doesn’t work. i.e. initialize the variable.
I can’t believe that in all the time I’ve concatenated stuff I haven’t stumbled onto this by accident. Really.
It’s like the go 10 .