Zero records appending to table.

I am trying to insert new records into table IV00101 with TEST apended to the end of the ITEMNMBR (Ex. ATIG1234TEST) , and only adding the ITEMNMBR’s  that match the ITEMNMBR in table SSG_temp_ATIitemsadd.  My script doesn’t error but it appends zero records. I believe it has to do with the WHERE Clause. If I Change the Column name in SSG_temp_ATIitemsadd to something that doen’t match the column name in IV00101 it appends TEST to the ITEMNMBRs in IV00101 but of course it appends all the ITEMNMBRs not just the ones that match.  
declare @Itmnmbr char(21)
set @Itmnmbr = 'TEST'

INSERT INTO [IV00101]
           (ITEMNMBR, ITEMDESC, NOTEINDX, ITMSHNAM, ITEMTYPE, ITMGEDSC, STNDCOST, CURRCOST, ITEMSHWT, DECPLQTY, DECPLCUR, ITMTSHID, 
                      TAXOPTNS, IVIVINDX, IVIVOFIX, IVCOGSIX, IVSLSIDX, IVSLDSIX, IVSLRNIX, IVINUSIX, IVINSVIX, IVDMGIDX, IVVARIDX, DPSHPIDX, PURPVIDX, UPPVIDX, 
                      IVRETIDX, ASMVRIDX, ITMCLSCD, ITMTRKOP, LOTTYPE, KPERHIST, KPTRXHST, KPCALHST, KPDSTHST, ALWBKORD, VCTNMTHD, UOMSCHDL, 
                      ALTITEM1, ALTITEM2, USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, MSTRCDTY, MODIFDT, CREATDDT, 
                      WRNTYDYS, PRCLEVEL, LOCNCODE, PINFLIDX, PURMCIDX, IVINFIDX, INVMCIDX, CGSINFLX, CGSMCIDX, ITEMCODE, TCC, PriceGroup, PRICMTHD, 
                      PRCHSUOM, SELNGUOM, KTACCTSR, LASTGENSN, ABCCODE, Revalue_Inventory, Tolerance_Percentage, Purchase_Item_Tax_Schedu, 
                      Purchase_Tax_Options, ITMPLNNNGTYP, STTSTCLVLPRCNTG, CNTRYORGN, INACTIVE, MINSHELF1, MINSHELF2, INCLUDEINDP)
    
          (SELECT    rtrim(ITEMNMBR) + @Itmnmbr, ITEMDESC, NOTEINDX, ITMSHNAM, ITEMTYPE, ITMGEDSC, STNDCOST, CURRCOST, ITEMSHWT, DECPLQTY, DECPLCUR, ITMTSHID, 
                      TAXOPTNS, IVIVINDX, IVIVOFIX, IVCOGSIX, IVSLSIDX, IVSLDSIX, IVSLRNIX, IVINUSIX, IVINSVIX, IVDMGIDX, IVVARIDX, DPSHPIDX, PURPVIDX, UPPVIDX, 
                      IVRETIDX, ASMVRIDX, ITMCLSCD, ITMTRKOP, LOTTYPE, KPERHIST, KPTRXHST, KPCALHST, KPDSTHST, ALWBKORD, VCTNMTHD, UOMSCHDL, 
                      ALTITEM1, ALTITEM2, USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4, USCATVLS_5, USCATVLS_6, MSTRCDTY, MODIFDT, CREATDDT, 
                      WRNTYDYS, PRCLEVEL, LOCNCODE, PINFLIDX, PURMCIDX, IVINFIDX, INVMCIDX, CGSINFLX, CGSMCIDX, ITEMCODE, TCC, PriceGroup, PRICMTHD, 
                      PRCHSUOM, SELNGUOM, KTACCTSR, LASTGENSN, ABCCODE, Revalue_Inventory, Tolerance_Percentage, Purchase_Item_Tax_Schedu, 
                      Purchase_Tax_Options, ITMPLNNNGTYP, STTSTCLVLPRCNTG, CNTRYORGN, INACTIVE, MINSHELF1, MINSHELF2, INCLUDEINDP
FROM  [IV00101]
WHERE  (ITEMNMBR in (SELECT     ITEMNMBR from SSG_temp_ATIitemsadd)) AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))
)

Open in new window

skull52Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi.

You are doing RTRIM(ITEMNMBR) which indicates to me you are expecting trailing spaces in ITEMNMBR in IV00101. Could the same be true for ITEMNMBR in SSG_temp_ATIitemsadd? If not, then possibly you are comparing ITEMNMBR columns with different amounts of trailing spaces.

Kevin
0
skull52Author Commented:
So are you suggesting I add rtrim to ITEMMNMBR on the SSG_temp_ATIitemsadd table?
0
Kevin CrossChief Technology OfficerCommented:
Possibly. I was actually thinking that table may have the value w/o trailing spaces, but because of the RTRIM() elsewhere in the query that the one at top-level needs to be trimmed. It is usually safer to RTRIM() both. For performance of any index on that field, the ultimate would be to change original data type to VARCHAR and trim spaces out of the data at rest. That way you don't have to remember which needs trimming.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

skull52Author Commented:
I can't really change the data type on the insert to table as it is an application generated table (Dynamics Great Plains) . I modified the WHERE Clause to include the RTRIM but still zero records inserted

WHERE  (ITEMNMBR in (SELECT rtrim(ITEMNMBR) from SSG_temp_ATIitemsadd)) AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))
)
0
Kevin CrossChief Technology OfficerCommented:
Okay, I am sorry. My iPad doesn't post as EE Mobile, but I was indeed not at my PC; therefore, I probably should have been more clear.

Your original query:
FROM  [IV00101]
WHERE  (ITEMNMBR in (SELECT     ITEMNMBR from SSG_temp_ATIitemsadd))
AND  (rtrim(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

The RTRIM() there makes me suspect that ITMNMBR in [IV00101] may have trailing spaces whereas the one in SSG_temp_ATIitemsadd does NOT or they have different amounts of spaces, e.g., one is a CHAR(20) and the other CHAR(30) -- varying length. Therefore, what I am suggesting is to put a RTRIM() on the ITEMNMBR from [IV00101]. To be safe, BOTH is fine too.

So:
FROM  [IV00101]
WHERE  (RTRIM(ITEMNMBR) in (SELECT ITEMNMBR from SSG_temp_ATIitemsadd))
AND  (RTRIM(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

Or:
FROM  [IV00101]
WHERE  (RTRIM(ITEMNMBR) in (SELECT RTRIM(ITEMNMBR) from SSG_temp_ATIitemsadd))
AND  (RTRIM(ITEMNMBR) + @Itmnmbr not in (SELECT ITEMNMBR FROM  [IV00101]))

Hope that makes sense.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52Author Commented:
Well I found the problem, I was using a spread sheet to populate the SSG_temp_ATIitemsadd table which indeed had excessive spaces at the end of each record, which I thought RTRIM would have removed, once I deleted the excessive spaces  from the spread sheet and re-imported the records all worked. Thanks mwvisa1 you lead me in the right direction.
0
Kevin CrossChief Technology OfficerCommented:
You are most welcome. Yes, there are some oddities there at times. I often suspect it is Alt+Enter or whatever the keystroke is for the carriage return/line feed that appears to be a space but is really a different character. Since RTRIM() only removes spaces, it does not work. Anyway, glad you found it.

Best regards and happy coding,

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.