Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Cast during an INSERT INTO SELECT statment

Posted on 2004-10-25
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12399362
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

Lowfatspread earned 1000 total points
ID: 12399852
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

610 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