Insert with Null

[bestmakis]

Hi

I have a table called Remote_Item with some records like

Code fBaseBOMGID Description
64167 C5F2EA4A-A374-464B-A001-BBBCA2EAFF8D Ear Plugs (5 Pairs)
64082 Null Ear Defenders
27940 9CF5E211-9A1F-42EA-B26E-C6693CED200D EAR PROTECTOR
93404 Null Folding Ear Defenders
26564 7959E6AA-2E52-415E-B6FB-64D6CA2EA280 SIGILL SPRAY ZINC
45589 A7859F70-453B-4F0B-BDBD-339A44488149 BOLT CUTTERS CRV 900mm
45885 4D396E23-EFE3-43C8-8E2A-090AE8704329 BOLT CUTTERS CRV 750mm
45876 868A057E-C43C-4B5C-B69D-D33C6ACF69D5 BOLT CUTTERS CRV 600mm
45869 Null BOLT CUTTERS CRV 450mm
45852 D3A74BBC-F947-4583-952D-D8E5FC89C40D BOLT CUTTERS CRV 350mm

Code = String
‌fCodeGID = unique identifier
‌Description = String

‌‌I create a loop so I can read these records and add them intoanother table.

Inside loop I build the insert string

‌‌Dim NewString AS String

NewString = "INSERT INTO LOCAL_Item VALUES (" & "'"& Code & "', '" & fBaseBOMGID & "', '"& Description & "'")

When the loop run working fine when all fields have data but I geterror when turns to read a null value…

For example.

I read the first line and sting become like…
‌

INSERT INTO LOCAL_Item VALUES ('64167','C5F2EA4A-A374-464B-A001-BBBCA2EAFF8D', 'Ear Plugs (5 Pairs)')

Working fine….

When I read the second line the string become

‌‌INSERT INTO LOCAL_Item VALUES ('64082','Null', 'Ear Defenders')

And I get error for Null value.

I try to use isnull function but I get the error
Conversion failed when convertingfrom a character string to uniqueidentifier.

How I can solve it?

Thank you.

 

[gmmastros]

Your problem is not uncommon.

The root of the problem is that there are single quotes around null. With single quotes around it, SQL Server sees it as a string containing the word null instead of the value null.

If the data type was varchar, the query would succeed and the word NULL would be inserted. Without the single quotes, the value null would be used instead. Since your column data type is uniqueidentifier, the word NULL is not acceptable.

When the value is null, remove the single quotes.

INSERT INTO LOCAL_Item VALUES ('64082',[!]'[/!]Null[!]'[/!], 'Ear Defenders')

‌‌Dim NewString AS String

NewString = "INSERT INTO LOCAL_Item VALUES (" & "'"& Code & "', '" & fBaseBOMGID & "', '"& Description & "'")

NewString = Replace(NewString, "'Null'", "NULL")

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

So what you have is this:

[pre]
Code fBaseBOMGID Description
64167 C5F2EA4A-A374-464B-A001-BBBCA2EAFF8D Ear Plugs (5 Pairs)
64082 <Null> Ear Defenders
27940 9CF5E211-9A1F-42EA-B26E-C6693CED200D EAR PROTECTOR
93404 <Null> Folding Ear Defenders
26564 7959E6AA-2E52-415E-B6FB-64D6CA2EA280 SIGILL SPRAY ZINC
45589 A7859F70-453B-4F0B-BDBD-339A44488149 BOLT CUTTERS CRV 900mm
45885 4D396E23-EFE3-43C8-8E2A-090AE8704329 BOLT CUTTERS CRV 750mm
45876 868A057E-C43C-4B5C-B69D-D33C6ACF69D5 BOLT CUTTERS CRV 600mm
45869 <Null> BOLT CUTTERS CRV 450mm
45852 D3A74BBC-F947-4583-952D-D8E5FC89C40D BOLT CUTTERS CRV 350mm
[/pre]
If you just want to "read these records and add them into another table", why not simply do:
[tt]
INSERT INTO LOCAL_Item VALUES (
SELECT Code, fBaseBOMGID, Description From Remote_Item )
[/tt]

Giasas


---- Andy

There is a great need for a sarcasm font.

 

[bestmakis]

Thank you all....

Both are perfect solutions....