So this is freaking great!!! I finally learned how to manipulate data by pulling bits and pieces out of a sql statement to use in other SQL statements. Please note that I have not tried this in SQL Server 2000 but it sure works great in SQL Server 2005 and up.

Let’s say that you want to add a column to a table and then populate it with data that is in the table already. (I know redundant data, however the thought at work is that it is primer data that the end user can change as opposed to a locked value that they can do nothing with.)

This script is going to be used in an upgrade to an existing application. So if the change has already happened we want to by pass this in lieu of the current data that is in the system.

First you want to check to see of your field exists and if it does not add it.

IF not exists (select * from sys.columns where [object_id] = (

Select [object_id] from sys.tables st JOIN sys.schemas ss ON st.schema_id = ss.schema_id where st.name = 'table_name')

AND name = 'new_field_name')

BEGIN

ALTER TABLE [dbo].[table_name] ADD [new_field_name] [char](12) NULL

END

GO

Because my in house example will create temporary tables I want to check if the temp tables exist and if so drop them

IF  EXISTS (SELECT name FROM tempdb..sysobjects WHERE name like '#Temp%')

drop table #Temp

GO

IF  EXISTS (SELECT name FROM tempdb..sysobjects WHERE name like '#Temp2%')

drop table #Temp2

GO

Note the ‘%’ in my statements above. It is necessary because Microsoft adds a lot of junk to the end of the temp table.

I know the following can go several different ways. So, I am giving you the quick and dirty how to. The next thing I did was set a declaration that will be used to pull the count total out for use later in the conditional logic.

Declare @t_intTotal Int

SELECT @t_intTotal=count(unique_field_name) FROM table_name WHERE new_field_name is not null

print @t_intTotal

With normal queries we are able to set the count(unique_field_name) as intCountFieldName and use the as variable from our recordset. Not so from within SQL server. To use the data from count(unique_field_name) I had to set @t_intTotal = count(unique_field_name). This makes it available later on in the process.

Next we have to declare some more variables that will be used if all of our new field_name fields are null. (Again, think quick and dirty. Feel free to clean it up if you like.)

Declare @Count Int

Declare @LoopCount Int

Declare @populate_value char(3)

Declare @t_populate_value char(3)

Using the new @t_intTotal variable we are able to write a simple if statement and run some code if it is necessary. After the first check we have our first of 2 temp tables. The data from here will be used to create the second temp table with an identity added to it.

IF @t_intTotal = 0

Select unique_field_name -- also the seed data value

into   #Temp

from  table_name

Using the first temp table we add the identity to a second temp table. This gives us a unique key that we can loop over when trying to add data to the table that already exists. We will set the Count to the total rows from the new query and set loop count to start at 1. Note if you pull an identity field in as one of your variables you cannot do the Identity(int,1,1) id. Only one identity field per table.

IF @t_intTotal = 0

Select Identity(int,1,1) ID, unique_field_name into #Temp2 from #Temp

Set @Count = @@RowCount

Set @LoopCount = 1

Using the second temp table we are able to loop over the recordset making an update with each iteration. In my case it is important to note that unique_field_name is a unique record so that we can use it to make sure the right seed data is going into the correct column.

IF @t_intTotal = 0

While @LoopCount <= @Count

Begin

Select @t_populatevalue=unique_field_name  from #Temp2 Where ID = @LoopCount

Set @LoopCount = @LoopCount + 1

update table_name

set new_field_name = @t_populate_value

where unique_field_name = @t_populate_value

End

GO

Note that I had to add unique_field_name data as the seed data in the new_field_name column.

This may be old hat for some, but when searching the web it is near impossible to find all of this in one place. I hope that you enjoy it and can mod it for your use. Let me know what you think.