CODEWITHSUNDEEP
sqlsql-server
user3271531
user3271531

Reputation: 13

SQL insert using select from .. where not exists and local variables in the select clause fails

I am trying to insert records into table B from table A, where the records don't already exist in table B. Only some of the fields I need are in table A, so I have set up some local variables to insert the data for these. On running the query below, I get the error message

"Msg 8114, Level 16, State 5, Line 17 Error converting data type varchar to numeric."

Would anyone be able to tell me what I am doing wrong, and perhaps provide an alternative method that would work. Many thanks (and apologies for the formatting of the query)

DECLARE @SupplierID as integer
DECLARE @UnitOfMeasurementID as integer
DECLARE @MinOrderQuantity as integer
DECLARE @SupplierProductGroupID as integer
DECLARE @ProductCondition as varchar (3)

SET @SupplierID = 1007
SET @UnitOfMeasurementID = 1
SET @MinOrderQuantity = 1
SET @SupplierProductGroupID = 41
SET @ProductCondition = 'N'


-- Insert

insert into tblProduct (SupplierID,
            UnitOfMeasurementID,
            MinOrderQuantity,
            SupplierProductGroupID,
            ProductCondition,
            PartNumber,
            ProductName,
            CostPrice)  
select 
    PartNumber,
    ProductName,
    CostPrice,
    @SupplierID, 
    @UnitOfMeasurementID,
    @MinOrderQuantity,
    @SupplierProductGroupID,
    @ProductCondition
from BearmachTemp source
where not exists
    (
        select * from tblProduct 
        where tblProduct.PartNumber = source.PartNumber
        and tblProduct.ProductName = source.ProductName
    )

Upvotes: 1

Views: 152

Answers (3)

xSQL Software
xSQL Software

Reputation: 156

Looking at your query it sounds like you are expecting SQL to match the fields by name which it DOES NOT. The names of the fields in the INSERT INTO clause are irrelevant - the matching will be done based on the order so in your case the value for PartNumber will go into SupplierID, the value for ProductName will go into UnitOfMeasurementID etc. Just change the order of the fields in either the insert into tblProduct(...) section or in the select ... section so that they match and you will be ok.

Upvotes: 1

Maryam Arshi
Maryam Arshi

Reputation: 2024

In Insert Statement Insert columns and values should have the same Order, so :

insert into tblProduct (SupplierID,
            UnitOfMeasurementID,
            MinOrderQuantity,
            SupplierProductGroupID,
            ProductCondition,
            PartNumber,
            ProductName,
            CostPrice)  
select 
    @SupplierID, 
    @UnitOfMeasurementID,
    @MinOrderQuantity,
    @SupplierProductGroupID,
    @ProductCondition,
    PartNumber,
    ProductName,
    CostPrice



from BearmachTemp source
where not exists
    (
        select * from tblProduct 
        where tblProduct.PartNumber = source.PartNumber
        and tblProduct.ProductName = source.ProductName
    )

Upvotes: 1

Michal Brašna
Michal Brašna

Reputation: 2323

SELECT has columns in wrong order

select 
    @SupplierID, 
    @UnitOfMeasurementID,
    @MinOrderQuantity,
    @SupplierProductGroupID,
    @ProductCondition,
    PartNumber,
    ProductName,
    CostPrice
from BearmachTemp source
where not exists
(
    select * from tblProduct 
    where tblProduct.PartNumber = source.PartNumber
    and tblProduct.ProductName = source.ProductName
)

Upvotes: 3

Related Questions