Append/Insert Question 2

[stnkyminky]

I have two tables (1) ZebraDB and (2) tbl_sw_bundles.

ZebraDB contains two columns ID_JOB and ID_BDL.
Sample Data
ID_JOB ID_BDL
C391A1 001
C391A1 002

tbl_sw_bundles contains three columns mybundle, Date_Shipped, and TruckName

Sample Data
myBundle Date_Shipped TruckName
C391A1-001 08/30/2002 UPS
C391A1-002 08/30/2002 UPS


tbl_sw_bundles is populated with data from zebradb via an append query

Insert into tbl_sw_bundles (mybundle)
select Bundle = (ID_JOB + '-' +ID_BDL) from ZebraDB
Where not exists (Select mybundle from tbl_sw_bundles)



The append works great until I Delete a row from tbl_sw_bundles. Delete from tbl_sw_bundles Where mybundle = 'C391A1-001' The next time I attempt to append new rows the select statement fails and find no new records even though C391A1-001 is in table zebradb Scott
Programmer Analyst
<><

 

[Antzz]

Try this query :

Insert into tbl_sw_bundles (mybundle)
select Bundle = ID_JOB + '-' + ID_BDL from ZebraDB
Where ID_JOB + '-' + ID_BDL not in (Select ltrim(rtrim(mybundle)) from tbl_sw_bundles)

if this does not work then try trimming the values of ID_JOB and ID_BDL since the values will not be same if there are extra spaces padded on to the values depending on the length of the columns.

Let me know if it worked.

 

[Sqlsa]

Hi,
Change the datatypes of columns ID_Job, ID_BDL, mybundle to VARCHAR (I expect them to be CHAR datatype now)if the values for ID_JoB and ID_BDL have varied lengths.

Otherwise trimming the data in these columns always help.
LTRIM may not be necessary, but RTRIM will be required.

Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[stnkyminky]

Thank you both for your solutions. Scott
Programmer Analyst
<><