Touble with query using a temp table to compare and update existing table

Question

Post reply

Touble with query using a temp table to compare and update existing table

eleduke

SSC Journeyman

Points: 78
More actions
June 16, 2016 at 6:59 am

#308531

Let me post the code and then the problems I am having with it.
if object_id('dbo.namefile', 'u') is not null
drop table dbo.namefile
create table dbo.namefile
(
name varchar(255) not null primary key
)
bulk insert dbo.namefile
from 'f:\list.csv'
with (datafiletype = 'char',
fieldterminator = '","',
rowterminator = '\r',
errorfile = 'f:\inp_err.log')
update dbo.MeasurementLimit
set LowLimit = 1
from namefile as nf
join EntityName as en on en.EntityName = nf.name
join MeasurementLimit as ml on en.uid = ml.UID
where en.EntityName = nf.name
If I run a simple query against the namefile table it will return 113 records There should be 112 1 blank record shows up don't know why...
but the real problem is if I run the above query or even this simple one
select * from namefile nf
join entityname en on nf.listname = en.entityname
where nf.listname = en.entityname
it will only return/update 1 record when there should be 112
Thanks for any help or suggestions there are to be had

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Check your column names.

What does the staging table contain? select * from namefile.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Maybe there is a terminator issue in the raw data file? there are char(10)s, char(13)s and a pair of them that can be used for line terminators and most file viewers will represent them the same to your eyeballs.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

eleduke SSC Journeyman Points: 78 More actions · Answer 3

A spreadsheet was originally generated from the entityname and measurementlimit tables. The csv file is generated from that spreadsheet so the names are correct. as you can see in these images

https://www.dropbox.com/s/uu5v6zd6v0m3hum/namefile.PNG?dl=0

https://www.dropbox.com/s/hp7buuas7zc2lqv/Entity.png?dl=0

eleduke SSC Journeyman Points: 78 More actions · Answer 4

I checked the raw file and did not see any extra special chr that would cause a LF/CR.. I was using Notepad++ to check it

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

eleduke (6/16/2016)
I checked the raw file and did not see any extra special chr that would cause a LF/CR.. I was using Notepad++ to check it

Choose a single value from your staging table dbo.namefile. Paste it into a query like this:

select * from entityname where entityname = 'copy value in here'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eleduke SSC Journeyman Points: 78 More actions · Answer 6

ok so

This works fine select * from EntityName e where e.EntityName = '17L-52W7 KVAR'

But this returned nothing select * from namefile n where n.Listname = '17L-52W7 KVAR'

So there seems to be a problem with the namefile table

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

eleduke (6/16/2016)
ok so
This works fine select * from EntityName e where e.EntityName = '17L-52W7 KVAR'
But this returned nothing select * from namefile n where n.Listname = '17L-52W7 KVAR'
So there seems to be a problem with the namefile table

The namefile staging table only contains 112 rows remember...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eleduke SSC Journeyman Points: 78 More actions · Answer 8

and 1 blank row that I don't know why its there so there are 112 Look at the attachment

Could this be causing the problem .. and I shouldn't have it to begin with it's a "no null" field

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

This remains unanswered:

ChrisM@Work (6/16/2016)
eleduke (6/16/2016)
I checked the raw file and did not see any extra special chr that would cause a LF/CR.. I was using Notepad++ to check it
Choose a single value from your staging table dbo.namefile. Paste it into a query like this:
select * from entityname where entityname = 'copy value in here'

Also, what do you see when you query the staging table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

eleduke SSC Journeyman Points: 78 More actions · Answer 10

2 posts up the sq.png shows the results

When I query the entityname table with a value copied from the namefile table I get the results I was looking for

but when I queried the namefile table with a value from the namefile table it came up with zip!!!?:w00t:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

eleduke (6/16/2016)
2 posts up the sq.png shows the results
When I query the entityname table with a value copied from the namefile table I get the results I was looking for
but when I queried the namefile table with a value from the namefile table it came up with zip!!!?:w00t:

You should reconsider Kevin's suggestion. Have a peek at RIGHT(1) and RIGHT(2) of the values in the staging table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

1) I think to see the second from last character you would need substring(field, len(field) - 1, 1) or some such. Use ASCII to see the character code.

2) Perhaps there is a non-parsable or bad/extra parsable character somewhere in the file?

Sorry but I am not able to follow along closely on this thread today. :ermm:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

eleduke SSC Journeyman Points: 78 More actions · Answer 13

I think there is something in the list file that is causing this headache. I added a second column to it to see if having a column delimiter would help some how but now I am getting a 4832 error so I am working through figuring out why I am getting the unexpected EOF.. then I will do as you suggest and see if I can find any extra stragglers in the table

frederico_fonseca SSCoach Points: 16154 More actions · Answer 14

2 comments.

when looking at a file that may contain invalid chars always use a hex editor - notepad++ and similar are not really good at this.

regarding the "empty" record try the following to see what the results are

select len(rtrim(ltrim(name))) as str_len

, replace(name, char(0), '') as str_no_nulls

, convert(varbinary(600), name) as str_hex

from dbo.namefile