Post reply

Error when copying pasting in Excel data to empty table

  • May 18, 2020 at 10:28 pm

    #3752741

    I created a table:

    CREATE TABLE dbo.orders
    (
        ord_no          int     NOT NULL,
        purch_amt       money   NOT NULL,
        ord_date        date    NOT NULL,
        customer_id     int     NOT NULL,
        salesman_id     int     NOT NULL
    )

    I right clicked on the table then selected Edit Top 200 Rows to open an empty table in the GUI.  I copied some sample data I have in an Excel file and attempted to paste that into the blank table.  I have done this many times but for some reason I am getting an error now.

    Attached I have a screenshot of the sample data from Excel as well as the error message.  The error appears to have something to do with the fact that by default, when the table opens, there are NULL values in each cell.  Although I am definitely pasting in values, for some reason SSMS thinks I am pasting a NULL into the ord_date column even though I have values in that column.

    In a past post, somebody said something about not attaching pictures showing my code.  The attached pictures do not contain code so I hope that is ok.

    Edit:  I deleted the table above and recreated it, except this time I did allow NULLs.  I pasted in my Excel data and all values in ord_date show up as NULL even though I definitely have values in my Excel file when I copy the sample data.  See the picture called Nulls for all Date Values.

    Why doesn't SSMS recognize the date values I am pasting in which are in the form of 41187, 41162, 41138, etc.?

    Attachments:
    You must be logged in to view attached files.

  • May 19, 2020 at 2:32 am

    #3752771

    You do realize that your dates that you're copying from aren't dates, right?  They probably the number of days since 1900-01-01 but they're not actually dates.  I believe that's your problem.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2020 at 11:25 am

    #3752817

    That makes sense.  I'm not sure what happened there, but this is helpful.  Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

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