Muller Jannie

About myself and everyone else.

loop-de-loop

Fancy loops,

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 )

Value (1,2)

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

Select @columnlist

 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  .

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on January 6, 2012 by in Uncategorized.
%d bloggers like this: