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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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:

  • 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

  • 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

  • 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

  • 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

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

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