[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1176
  • Last Modified:

Cast during an INSERT INTO SELECT statment

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?



0
esager1
Asked:
esager1
1 Solution
 
OlegPCommented:
try it

INSERT INTO tableB (fiID, Qty, Size)
SELECT     identity_column,
                CAST(lot AS FLOAT) AS Expr1,
                onhand AS Expr2
FROM        tableA
OPTION (FORCE ORDER)
0
 
LowfatspreadCommented:
are you haveing problems with "null" or empty values...

try
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now