none
adding values in two column with T-SQL

    Question

  • Hi 

    I am moving data from staging to the destination and there are two columns called Col 1 and Col2. I want to add both the columns and put them in the destination in one column. So what T-SQL should i use for this purpose.

    BY adding i mean if col1 has 3 and column2 has 4 i want to put them in destination as 3+4 =7

    actually iam using SSIS and at the OLEDB source i want to put the SQL COMMAND. SO please tell me how to achieve this.

    OR 

    If you guys know some another method??

    Thanks in Advance


    Fighttillend_DBA/DEV
    • Edited by SQL_BOSS Monday, June 13, 2011 10:16 PM
    Monday, June 13, 2011 10:08 PM

Answers

  • SQL_BOSS,

    Either you can use Script Component to that, check my sample or an Aggregate component from Component list in SSIS, or Derived Column Component or in OLE DB Source as Naomi said do that.

    If you want to use OLE DB Source make sure in WHERE clause check ISNUMERIC() for those two columns.

    Like:

    Where 
    IsNumeric(Col1) = 1 And IsNumeric(Col2) = 1
    

     But this will filter your whole data source. Make sure you do NOT need anything that is not numeric for Col1 and Col2.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Tuesday, June 21, 2011 8:40 AM
    Monday, June 13, 2011 10:59 PM

All replies

  • Do you want something like this ????

     

    Declare @t Table (ID Int,Price1 int,Price2 int,[Type] varchar(10))
    Declare @t1 Table (ID int ,TotalPrice int,[type] varchar(10))
    
    Insert into @t Select 1,10,10,'X'
    Insert into @t Select 2,20,10,'X'
    Insert into @t Select 3,30,10,'X'
    Insert into @t Select 4,40,10,'X'
    
    Select * from @t
    
    Insert into @t1 Select ID,Price1+Price2,[type] from @t
    
    Select * from @t1
    
    



    If this answer is helpful to you .. Please mark as Answer....
    Monday, June 13, 2011 10:15 PM
  • ' Microsoft SQL Server Integration Services Script Component
    ' Write scripts using Microsoft Visual Basic 2008.
    ' ScriptMain is the entry point class of the script.
    
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Imports System.Data.SqlClient
    
    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
     Inherits UserComponent
    
     Dim connMgr As IDTSConnectionManager100
     Dim sqlConn As SqlConnection
     Dim mySqlCommand As SqlCommand
     Dim myParam As SqlParameter
    
     Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
      connMgr = Me.Connections.Connection
      sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
     End Sub
    
     Public Overrides Sub PreExecute()
      Dim strSql As String
    
      strSql = "Update myDestination "
      strSql = strSql & " Set ColUpdated = @Col1 + @Col2 "
      strSql = strSql & " Where ColId = @ColId "
    
      mySqlCommand = New SqlCommand(strSql, sqlConn)
      mySqlCommand.CommandTimeout = 0
    
      myParam = New SqlParameter("@ColID", SqlDbType.Int)
      mySqlCommand.Parameters.Add(myParam)
    
      myParam = New SqlParameter("@Col", SqlDbType.Int)
      mySqlCommand.Parameters.Add(myParam)
    
      myParam = New SqlParameter("@Col2", SqlDbType.Int)
      mySqlCommand.Parameters.Add(myParam)
    
     End Sub
    
     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
      Dim reader As Object
    
      With mySqlCommand
       If Not Row.ID_IsNull Then .Parameters("@ColID").Value = Row.ID Else .Parameters("@ColID").Value = 0
       If Not Row.Col1_IsNull Then .Parameters("@Col1").Value = Row.Col1 Else .Parameters("@Col1").Value = 0
       If Not Row.Col2_IsNull Then .Parameters("@Col2").Value = Row.Col2 Else .Parameters("@Col2").Value = 0
       reader = .ExecuteNonQuery()
      End With
    
    
     End Sub
    
    End Class
    
    
    
    --------------------------------------------------------------------------------
    
    Customize the above code in SSIS, script component. Pass your column and connection there rest will be in above code.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Monday, June 13, 2011 10:18 PM
  •  i think farhan my case is different but i will test it .

    I have made some changes in the details above. But thanks. I will update you. 

    BY adding i mean if col1 has 3 and column2 has 4 i want to put them in destination as 3+4 =7


    Fighttillend_DBA/DEV
    Monday, June 13, 2011 10:19 PM
  • I want to make sure is this will work for me

    select sum(Col1  + Col 2) as GrandTotal


    Fighttillend_DBA/DEV
    Monday, June 13, 2011 10:21 PM
  • This should work and the result should be the same as

    SUM(Col1) + SUM(Col2) as GrandTotal (better to have both fields as decimal and not money, though).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, June 13, 2011 10:25 PM
    Moderator
  • Naomi 

    I have them as Varchar and i am getting error that both are varchar so now what should i do


    Fighttillend_DBA/DEV
    Monday, June 13, 2011 10:32 PM
  • Can you then show some data and what result do you want to get? How do you want to 'sum' varchar fields - do you want to concatenate a string?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, June 13, 2011 10:34 PM
    Moderator
  • Naomi  I mean

    BY adding i mean if col1 has 3 and column2 has 4 i want to put them in destination as 3+4 =7

    i do not want concatination the Tsql script which you will give for this i will put in the SSIS in SQL Commant at the Oledb Source.



    Fighttillend_DBA/DEV
    Monday, June 13, 2011 10:44 PM
  • SQL_BOSS,

    Either you can use Script Component to that, check my sample or an Aggregate component from Component list in SSIS, or Derived Column Component or in OLE DB Source as Naomi said do that.

    If you want to use OLE DB Source make sure in WHERE clause check ISNUMERIC() for those two columns.

    Like:

    Where 
    IsNumeric(Col1) = 1 And IsNumeric(Col2) = 1
    

     But this will filter your whole data source. Make sure you do NOT need anything that is not numeric for Col1 and Col2.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Tuesday, June 21, 2011 8:40 AM
    Monday, June 13, 2011 10:59 PM