Unable to insert records into IN_UnitOfMeasure

[Atronics]

I get the following error when running this insert query. Despite several hours searching for a solution, I am stuck. Can you advise the solution, please.

error = "Msg 8169, Level 16, State 2, Line 31
Conversion failed when converting from a character string to uniqueidentifier."

Query
INSERT IN_UnitOfMeasure (RecID,IN_Main_InventoryID,Name, Inner_IN_UnitOfMeasure_RecID, QuantityInnersPerUnitOfMeasure, IsSell, IsPurchase,PartNo, Barcode, IsEnabled, ItemNo, LastSavedDateTime)
SELECT
CAST(NEWID() AS UNIQUEIDENTIFIER),
IN_Main.InventoryID,
'Bag',
'',
AA_IN_OrderUnits.OrderUnits,
1,
1,
LTRIM((RTRIM(IN_Main.PartNo)) +'B'),
'',
1,
1,
GETDATE()
FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID

Table AA_IN_OrderUnits is a custom table. Also tried wwithout the CAST function and received the same error.

[Scott.Pearce]

You are supplying an empty string for the "Inner_IN_UnitOfMeasure_RecID" value. You should provide a NULL. Also, you need not cast the output of NEWID() to UNIQUEIDENTIFIER (It is already a UNIQUEIDENTIFIER).

Code: Select all

INSERT IN_UnitOfMeasure (RecID,IN_Main_InventoryID,Name, Inner_IN_UnitOfMeasure_RecID, QuantityInnersPerUnitOfMeasure, IsSell, IsPurchase,PartNo, Barcode, IsEnabled, ItemNo, LastSavedDateTime)
SELECT
NEWID(),
IN_Main.InventoryID,
'Bag',
NULL,
AA_IN_OrderUnits.OrderUnits,
1,
1,
LTRIM((RTRIM(IN_Main.PartNo)) +'B'),
'',
1,
1,
GETDATE()
FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID

[Atronics]

Thanks, Scott.

[Mike.Sheen]

FYI - I notice your join is using a non-ANSI join syntax:

FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID

I believe at some point such joins won't be supported anymore (I think I read that in the SQL 2005 release notes - so who knows when they'll stop supporting it if it still works today).

The ANSI join would be:

Code: Select all

FROM IN_Main
JOIN AA_IN_OrderUnits ON IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID

It's probably best to get into the habit of using ANSI joins sooner rather than later.