Updating a Dynamically added column in a Table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating a Dynamically added column in a Table.

Author

Topic

vicky
Starting Member

3 Posts

Posted - 2006-07-03 : 01:28:18

Hi All,
Following is my query, i need to update a particular column which is getting dynamicaaly added to the table. My Problem is might be sometimes that column might not be there. So how to get into that..

QUERY :

WHILE (@noOfFormulaId > 0)
BEGIN
SELECT @FormulaCodeString = rtrim(ltrim(EVMSDataElement))
From #tempGrPriceData1
Where RowNum = @noOfFormulaId

SELECT @NAME = '['+@FormulaCodeString+ ']'
SELECT @QUERY = 'ALTER TABLE #tmpData ADD ' + @NAME + ' decimal(15,2)'

EXEC(@QUERY)

Select @NoOfRows=Count(*) from #tmpData

While(@NoOfRows>0)
Begin
Select @EVMSID = EVMSID, @ACWPID = ACWPID, @MasterProjectId = MasterProjectId,
@WorkpackageId = WorkpackageId, @SubResourceId = OBSRESOURCEID, @CostCategoryId = CostCategoryId,
@TrendId = TrendId, @PeriodTypeDateId = PeriodTypeDateId, @RateBasisCodeId = RateBasisCodeId,
@AssignmentTypeId = ASSIGNMENTTYPEID, @OTAMOUNT = OTAMOUNT, @OTHOURS = OTHOURS,
@STHOURS = STHOURS, @STAMOUNT = STAMOUNT, @TotalAmount = TotalAmount
From #tmpData where RowNum = @NoOfRows

Insert Into #formulaValue
Execute PS_ReplaceFormulaElementsWithValues @FormulaCodeString ,@MasterProjectId,
@WORKPACKAGEID , @SubResourceId, @COSTCATEGORYID , @TRENDID ,@PeriodTypeDateId ,
@RateBasisCodeId , @AssignmentTypeId , @OTAMOUNT ,
@OTHOURS , 0 , @STHOURS ,
@STAMOUNT , 0 , @TotalAmount

If @@Error <> 0
Begin
Rollback Transaction Trans
Return 1
End

Insert into #TempPrice
Select @EVMSID,@ACWPID,Formulavalue,@FormulaCodeString,@RateBasisCodeId from #formulaValue
Select @NoOfRows=@NoOfRows-1
Delete From #formulaValue
End

SELECT @QUERY = 'UPDATE t SET ' + @NAME + ' =' + 'a.Formulavalue From #TempPrice a, #tmpData t
Where t.ACWPId=a.ACWPId and Isnull(t.RateBasisCodeId,0) = Isnull(a.RateBasisCodeId,0) and a.ColumnName ='''+Convert(varchar,@FormulaCodeString)+''''

EXEC(@QUERY)
SELECT @QUERY = 'UPDATE #tmpData SET ' + @NAME + ' =' + '0 Where '+ @NAME +'is null'

If @@Error <> 0
Begin
Rollback Transaction Trans
Return 1
End

EXEC(@QUERY)

SELECT @QUERY = '
Insert Into #tempSaveData
Select a.EvmsId, a.ACWPId, c.EVMSFormulaid, a.Formulavalue, t.ResourcePA
From #TempPrice a, #tmpData t,#tempGrPriceData c
Where t.ACWPId=a.ACWPId and Isnull(t.RateBasisCodeId,0) = Isnull(c.RateBasisCodeId,0) and Isnull(t.RateBasisCodeId,0) = Isnull(a.RateBasisCodeId,0) and a.ColumnName = c.EvmsDataelement and a.ColumnName ='''+Convert(varchar,@FormulaCodeString)+''''

EXEC(@QUERY)

/* I have to update here the column named "PA" in the table #tempSaveData. Sometimes that column might be missing. So aacordingly i had to update the same.*/

If @@Error <> 0
Begin
Rollback Transaction Trans
Return 1
End

Select @dynamicCloumnTracker =Convert(varchar(1000),@dynamicCloumnTracker) + '+isnull('+ convert(varchar,@NAME)+',0)'

SELECT @noOfFormulaId=@noOfFormulaId-1

Truncate Table #formulaValue
Truncate Table #TempPrice

If @@Error <> 0
Begin
Rollback Transaction Trans
Return 1
End
END

Thanks & Regards,

Winners are made by Attitude.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 02:16:15

Instead if EXEC(@QUERY), if you just do PRINT @QUERY, what does the queries look like?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 02:27:39

Is your table normalised?
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

vicky
Starting Member

3 Posts

Posted - 2006-07-03 : 03:21:45

Hi Peso,

In the query which i have written, Line No 7 is adding the columns dynamically in the table #tmpData. Then after i have fetched the data in to table #tempSaveData from table #tmpData.

So the column "PA" which needs to be updated is added in the table #tmpData and in turn to the table #tempSaveData. This column might be missing sometimes depending upon the conditions.

Now i have to check for the presence of column and then update it, otherwise nothing to be done.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-03 : 12:00:36

This is the very definition of making things way more complicated than they need to be .... if you consider normalizing your schema, then it is very simple and easy to add/remove data from your tables.

vicky
Starting Member

3 Posts

Posted - 2006-07-14 : 01:03:17

Hi Smith,

In the query which i have written, Line No 7 is adding the columns dynamically in the table #tmpData. Then after i have fetched the data in to table #tempSaveData from table #tmpData.

So the column "PA" which needs to be updated is added in the table #tmpData and in turn to the table #tempSaveData. This column might be missing sometimes depending upon the conditions.

Now i have to check for the presence of column and then update it, otherwise nothing to be done.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-14 : 07:21:22

Again, are you familiar with normalization? You have a very bad data model and it is making something that is normally easy very hard to do ... don't you think there must be an easier way to add data to a table rather than dynamically editing the table structure and created dynamic sql statements and executing them ?

A good data model lets you add new data simply by writing:

INSERT INTO YourTable (Year, Month, Value) VALUES (2005,12,1.1)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources