Cast during an INSERT INTO SELECT statment

Posted on 2004-10-25
Last Modified: 2012-05-05
Two tables, A has existing inventory information, B is a table added during a recent product upgrade to hold additional information.  B currently empty and want to create records and populate.

Using a query such as :

INSERT INTO tableB (fiID, Qty, Size)
SELECT     identity_column, 0 , 0
FROM         tableA

works fine, populates the fiID and sets Qty and Size to zero

Want to populate Qty and Size with correct values from tableA.

TABLE A                   TABLE B
-----------------          ----------------
ONHAND (Numeric)   QTY (Int)         [want it to be ONHAND/LOT]
LOT  (Chr)               SIZE (Numeric)

If I do a SELECT statment such as :

SELECT     CAST(lot AS FLOAT) AS Expr1, onhand / CAST(lot AS float) AS Expr2
FROM         tableA

The results are what I expect and the math works to populate QTY

If I try to do it in the INSERT INTO statment at the begining like this:

INSERT INTO tableB (fiID, Qty, Size)
SELECT     identity_column,
                CAST(lot AS FLOAT) AS Expr1,
                onhand AS Expr2
FROM        tableA

It fails on the conversion of LOT.  
ERROR COVERTING DATATYPE VARCHAR TO FLOAT or anything else I try to cover it to.  What is differnt between the SELECT statment and the INSERT INTO that makes it fail to convert and any ideas on a way around it?

Question by:esager1
    LVL 6

    Expert Comment

    try it

    INSERT INTO tableB (fiID, Qty, Size)
    SELECT     identity_column,
                    CAST(lot AS FLOAT) AS Expr1,
                    onhand AS Expr2
    FROM        tableA
    LVL 50

    Accepted Solution

    are you haveing problems with "null" or empty values...

    INSERT INTO tableB (fiID, Qty, Size)
    SELECT     identity_column,
                    convert(float, case when isnumeric(lot)=1 then lot else '0' end) Expr1,
                    onhand AS Expr2
    FROM        tableA

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now