SQL Insert only the rows that have data in a specific column

Question

I am basically a noob at this and have gotten this far from Google searches alone. Access VBA and SQL inventory database.

I have a table that I populate by a barcode scanner that looks like the following;

PartNo | SerialNo | Qty | Vehicle
-------+----------+-----+---------
test   |          | 1   | H2
test2  |          | 1   | H2
test3  | test3s/n | 1   | H2
test3  | test4s/n | 1   | H2
test   |          | 1   | H2

I am trying to update 2 tables from this, or insert if the PartNo doesn't exist.

tblPerm2 has PartNo as primary key
tblPerm1 has PartNo, SerialNo, Qty and Vehicle
PartNo must exist in tblPerm2 to be added to tblPerm1

I can get the PartNo inserted into tblPerm2 no problem, but I'm running into problems with tblPerm1.

I'm following user Parfait's example here, Update Existing Access Records from CSV Import , native to MS Access or in VB.NET

I've tried an Insert and and insert with a join. The code below adds everything to tblPerm1, including rows with no SerialNo. How can I insert only the rows from tblTemp that have a serial number?

INSERT INTO tblPerm1 (PartNo, SerialNo, Qty, Vehicle) 
    SELECT tblTemp.PartNo, tblTemp.SerialNo, tblTemp.Qty, tblTemp.Vehicle 
    FROM tblTemp 
    WHERE tblTemp.SerialNo IS NOT NULL;

I expect this to only insert the 2 'test3' rows, but all rows are inserted. SELECT DISTINCT is the same, but only one entry for 'test'

Once this is done, I'll delete from tblTemp and continue on updating and inserting. Maybe there is a better way?

Thanks in advance

Phoenix · Accepted Answer · 2019-07-23 03:36:39Z

0

Are the SerialNo columns actually empty strings instead of NULL?

If this works, then yes they are:

INSERT INTO tblPerm1 (PartNo, SerialNo, Qty, Vehicle) 
    SELECT tblTemp.PartNo, tblTemp.SerialNo, tblTemp.Qty, tblTemp.Vehicle 
    FROM tblTemp 
    WHERE tblTemp.SerialNo <> '';

See How to check for Is not Null And Is not Empty string in SQL server? for more on checking for empty strings, with or without counting whitespace (though details may vary depending on what SQL server you are running).

answered Jul 23, 2019 at 3:36

Phoenix

1291 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary Melancon Over a year ago

That was it, I guess it is the way I'm importing the data from the spreadsheet. I'm using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, [tblTemp], strPath, True

Collectives™ on Stack Overflow

SQL Insert only the rows that have data in a specific column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related