none
SQL refresh is duplicating items upon refresh

    Question

  • Good Morning,

    I have a database for ordering and budget that I have been using for the past year and it has worked great for our department.   My boss wanted to rename the products (our original file was very sloppy and unorganized).  To do this, I reopened the original file, and changed the names of the items in the original table...previously, we have been able to rename items individually and haven't experienced this issue.  We just waited to update all the names with the new fiscal year because renaming a row resulted in deleting data from the expanded columns on SQL queries.  

    This is an example of the master spreadsheet we manually changed the item names on

    Item Item Number Pkg Count Store Category Food Type
    Brown Sugar, Packet 809330 96 GFS Grocery Condiments
    Chia     Trader Joes Grocery Condiments
    Cocoa Powder Each Kroger Grocery Condiments
    Cream Cheese, Single Serve 257737 100 GFS Grocery Condiments
    Flax   Kroger Grocery Condiments
    Flour, Whole Wheat   5-lb bag Kroger Grocery Condiments
    Honey Packets 270539 1000 GFS Grocery Condiments
    Hot Cocoa Mix   GFS Grocery Condiments
    Jelly, Grape, Bulk 224111 6 GFS Grocery Condiments
    Jelly, Strawberry, Bulk 224081 6 GFS Grocery Condiments
    Ketchup, Bottle 214551 16-14oz bottle GFS Grocery Condiments
    Ketchup, Packets 852406   GFS Grocery Condiments
    Mayo 700011 200 GFS Grocery Condiments

    When the data refreshes, only items with a value in "Item Number" duplicate, and they multiply each time you refresh (the column values have shifted over when I copy/pasted, but it won't let me add a screen snip to this post)

    Teams Item Item Number Pkg Count Store Category Index Food Type
    Nutter Cocoa Powder Each Kroger Grocery 2 Condiments
    Nutter Flax Kroger Grocery 4 Condiments
    Nutter Flour, Whole Wheat 5-lb bag Kroger Grocery 5 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Bulk 1111073932 4 lbs Kroger Grocery 14 Condiments
    Nutter Peanut Butter, Single Serve, Jif Natural 8 1.5 oz cup Kroger Grocery 17 Condiments

    Any idea why it would be happening with the new spreadsheet?   Again, I haven't changed the SQL commands at all and the name changes were relatively minor, so "Peanut Butter, Bulk" used to be "Bulk Peanut Butter".   All other columns with corresponding data for each row would have remained identical.  I'm happy to share more/all of the spreadsheet if need be.

    Thank you!

    Thursday, June 28, 2018 1:50 PM

All replies

  • Can you post sample data  along with desired result?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, June 29, 2018 3:13 AM
    Moderator
  • Thank you for your response.

    This is a link to the document.

    https://1drv.ms/x/s!Ao7p3TYttj1XljT0dcYBO-IBxqF1

    The "Items Master" tab is used to update which items we purchase for each of our stores.   It links to "Orders" (so as we chose new products our order list will update).   Our budget management is a bit confusing because we order from multiple store, for multiple teams all on single orders (so we need to be able to sum up total quantity needed as well as break down cost per team once we get the receipt).  

    For background here is how the whole spreadsheet flows:

    Items Master: Master database

    Orders: Used to enter orders for each team each week

    Prices: Used to enter prices (and subsequently calculate spending), we need this sheet because we don't have fixed pricing

    Weekly Cost Allocation: Putting it all together

    So we renamed items on "Items Master", but didn't change column formats or anything.   Now, every time we refresh "Orders" will double the lines for each item with an "Item Number" (Column) value recorded.   I have no idea why it started doing this, and maybe the problem has nothing to do with item number - that's just the trend that I have noticed.     

    So for refreshing from Kroger "Peanut Butter, Bulk" will have 2 lines whereas "Peanut Butter, Single Serve, Jif Natural" will not.

    I want to fix it so that each item only represents one line regardless of the times we refresh.

    Friday, June 29, 2018 3:46 AM
  • Hi ShaeCarson,

     

    Could you please tell us whether you updated the data via linkserver or openrowset. If so, please share us the query statements.

     

    Besides, the primary key and foreign keys of the ItemsMaster and Order tables are also important. It will be appreciated if you can list the primary key and foreign keys of the two tables.

     

    Finally, if possible, please provide us the sample data and desired results to help us analysis this problem. It is enough to share 5 rows data related to the issue. Need to point out that please mask your private information.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 29, 2018 9:51 AM
  • Hi Emily,

    I am not very familiar with SQL (this is the only project I've ever used it on and when I created the document last year I sort of stumbled my way into it using YouTube videos, so my methods/understanding are limited).  That being said, I will do everything I can to help...

    I think it's "openrowset".   All the data is housed within the file (vs added to a server)

    I don't know what the primary and foreign keys are.  I've tried googling this to figure out how to share them with you, but can't seem to figure that out.   Can you point me closer into the direction of what you need?   I can share the Queries and dependencies if that would help?

    The sample data and desired result can be seen in my original post.  The blue is from Items Master, I need the green to mirror it exactly.  So the error is that the "Peanut Butter, bulk" is listed 4 times (resulted from 2 refreshes) vs just 1 time like all the other items.

    Friday, June 29, 2018 6:50 PM
  • Hello,

    I have had a look at your Excel Workbook: No connection points to a SQL Server, all connections are internal pointing to different sheets; that's really nothing SQL Server related.

    I also refresh the complete Workbook (without errors, because there is not SQL link/query) and I don't get additional duplicates.

    So either someone entered the "duplicates" manually or you are working with a buggy Excel installation/version.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Saturday, June 30, 2018 4:35 AM
    Moderator
  • Thank you for your feedback.   What might be a more appropriate forum to post this issue in - like if it isn't SQL (which I always thought it was since it runs queries) what sort of spreadsheet do I have set up?  I just tried to redownload the document from the link that I posted and again attempted to edit, then save and refresh.   No errors occur upon refresh, but I continued to see the duplicate row for those items with an item number listed.  I have also tried this on multiple computers (to ensure it's not just an issue with mine).

    Monday, July 2, 2018 11:25 PM
  • Hi ShaeCarson,

     

    It seems that your issue is more related to excel. I would suggest you posting your issue in the forum about excel. By doing so, you could get the more professional answer.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 3, 2018 9:08 AM