none
How can I INSERT INTO from Staging Table to Production Table RRS feed

  • Question

  • I’ve got a Bulk Load process which works fine, but I’m having major problems downstream.

    Almost everything is Varchar(100), and this works fine.  Except for these fields:

    INDEX SHARES, INDEX MARKET CAP, INDEX WEIGHT, DAILY PRICE RETURN, and DAILY TOTAL RETURN

    These four fields must be some kind of numeric, because I need to perform sums on these guys.


    Here’s my SQL:

    CREATE TABLE [dbo].[S&P_Global_BMI_(US_Dollar)]

    (

    [CHANGE]      VARCHAR(100),

    [EFFECTIVE DATE]   VARCHAR(100),

    [COMPANY]   VARCHAR(100),

    [RIC]   VARCHAR(100),

     

    Etc.

     

    [INDEX SHARES] NUMERIC(18, 12),

    [INDEX MARKET CAP] NUMERIC(18, 12),

    [INDEX WEIGHT] NUMERIC(18, 12),

    [DAILY PRICE RETURN] NUMERIC(18, 12),

    [DAILY TOTAL RETURN] NUMERIC(18, 12),

    )

     

    From the main staging table, I’m writing data to 4 production tables.

    CREATE TABLE [dbo].[S&P_Global_Ex-U.S._LargeMidCap_(US_Dollar)]

    (

    [CHANGE]      VARCHAR(100),

    [EFFECTIVE DATE]   VARCHAR(100),

    [COMPANY]   VARCHAR(100),

    [RIC]   VARCHAR(100),

     

    Etc.

     

    [INDEX SHARES] FLOAT(20),

    [INDEX MARKET CAP] FLOAT(20),

    [INDEX WEIGHT] FLOAT(20),

    [DAILY PRICE RETURN] FLOAT(20),

    [DAILY TOTAL RETURN] FLOAT(20),,

    )

     

     

    INSERT INTO [dbo].[S&P_Global_Ex-U.S._LargeMidCap_(US_Dollar)]

      SELECT

                [CHANGE],

    Etc.

     

                [DAILY TOTAL RETURN]

      FROM [dbo].[S&P_Global_BMI_(US_Dollar)]

      WHERE isnumeric([Effective Date]) = 1

      AND [CHANGE] is null

      AND [COUNTRY] <> 'US'

      AND ([SIZE] = 'L' OR [SIZE] = 'M')

     

     

    The Bulk Load is throwing errors like this (unless I make everything Varchar):

    Bulk load data conversion error (truncation) for row 7, column 23 (INDEX SHARES).

    Msg 4863, Level 16, State 1, Line 1

     

    When I try to load data from the staging table to the production table, I get this.

    Msg 8115, Level 16, State 8, Line 1

    Arithmetic overflow error converting varchar to data type numeric.

    The statement has been terminated.

     

    There must be an easy way to overcome this, right.

    Please advise!

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Tuesday, July 8, 2014 5:56 PM
    Tuesday, July 8, 2014 5:55 PM

Answers

  • Using your test data, this works:

    DECLARE @indexShares TABLE (indexShares VARCHAR(100))
    INSERT INTO @indexShares ( indexShares )
    VALUES  
    (-0.900900901),(9.302325581 ),(-2.648171501),(-1.402805723),
    (-2.911830584),(-2.220960866),(2.897762349 ),(-0.219640074),
    (-5.458448607),(-0.076626094),(6.710940231 ),(0.287200186 ),
    (0.131682908 ),(0.124276221 ),(0.790818723 ),(0.420505119)
    
    SELECT CONVERT(decimal(38,12),indexShares) 
    FROM @indexShares

    • Marked as answer by ryguy72 Tuesday, July 8, 2014 9:24 PM
    Tuesday, July 8, 2014 9:01 PM

All replies

  • You need to figure out what's causing the errors. You can use isNumeric() to identify the rows like this:

    SELECT * 
    FROM [dbo].[S&P_Global_BMI_(US_Dollar)]
     WHERE IsNumeric([Index Shares]) = 0

    Once you figure out what the issue is, post back and I probably have something that will help you fix it.

    It might be a good plan to make any conversions on the initial load into staging.

    Tuesday, July 8, 2014 6:41 PM
  • Nothing is returned.  Everything is VARCHAR(100).  the problem is this.

    If I use FLOAT(18) or REAL, I get exponential numbers, which is useless to me.

    If I use DECIMAL(18,12) or NUMERIC(18,12), I get errors. 

    Msg 4863, Level 16, State 1, Line 41
    Bulk load data conversion error (truncation) for row 7, column 23 (INDEX SHARES).
    Msg 4863, Level 16, State 1, Line 41
    Bulk load data conversion error (truncation) for row 8, column 23 (INDEX SHARES).
    Msg 4863, Level 16, State 1, Line 41
    Bulk load data conversion error (truncation) for row 9, column 23 (INDEX SHARES).


    There must be some data type that fits this!

    Here's a sample of what I'm dealing with.

    -0.900900901
    9.302325581
    -2.648171501
    -1.402805723
    -2.911830584
    -2.220960866
    2.897762349
    -0.219640074
    -5.458448607
    -0.076626094
    6.710940231
    0.287200186
    0.131682908
    0.124276221
    0.790818723
    0.420505119


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.



    • Edited by ryguy72 Tuesday, July 8, 2014 7:08 PM
    Tuesday, July 8, 2014 7:02 PM
  • Are you certain that you want numeric (18,12)? That only gives 6 places left of the decimal. That seems fine for the weighting factor, but when you deal with index shares and market cap, depending on if you receive numbers in millions or some other scale, you may not be allowing enough decimals.
    Tuesday, July 8, 2014 7:14 PM
  • No, I am not certain.  ;)

    I'm trying to figure out how to do this.

    I guess the LEN of characters could be 1 (which is a zero) up to 12.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Tuesday, July 8, 2014 7:40 PM
    Tuesday, July 8, 2014 7:40 PM
  • Using your test data, this works:

    DECLARE @indexShares TABLE (indexShares VARCHAR(100))
    INSERT INTO @indexShares ( indexShares )
    VALUES  
    (-0.900900901),(9.302325581 ),(-2.648171501),(-1.402805723),
    (-2.911830584),(-2.220960866),(2.897762349 ),(-0.219640074),
    (-5.458448607),(-0.076626094),(6.710940231 ),(0.287200186 ),
    (0.131682908 ),(0.124276221 ),(0.790818723 ),(0.420505119)
    
    SELECT CONVERT(decimal(38,12),indexShares) 
    FROM @indexShares

    • Marked as answer by ryguy72 Tuesday, July 8, 2014 9:24 PM
    Tuesday, July 8, 2014 9:01 PM
  • Yes, that works Patrick.

    It was a few 'E+' and 'E-' characters in the data set.  At some point, this text file must have been opened in Excel, and saved at a text file.  That's the only way those characters could be introduced.  Either the file came to me that way, or maybe I even did it, as I was viewing the data (easier to read in Excel than in Text file).  Too much multitasking these days.

    Thanks everyone!  


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, July 8, 2014 9:24 PM