Post reply

missing records on table

  • November 16, 2011 at 3:32 pm

    #390879

    Hello All,

    I was asked a question by someody for which I did not have answer and still couldn't figure out the reason. Here is the scenario. We have table A and table B. We truncate the table A and table B has the 100 rows. Now I want to copy all the records from table B to table A running the SQL script like this:

    INSERT INTO A

    SELECT * FROM TABLE B

    And the above statement only copies the 90 rows to the table A while table B has 100 records. Now table A is missing 10 records. What could be the possible issues? No other information about the table (DDL) is provided.

    Thank you all in adavnce for you time.

  • November 16, 2011 at 11:11 pm

    #1408965

    It's not possible. You might be inserting in a table & fetching from another (it generally happens with default schema). Please verify it & if possible, provide us some data, DDL to reproduce the issue.

  • November 17, 2011 at 3:05 am

    #1409047

    The only thing I can think of is that a trigger on table A is deleting some of the rows, or modifying what is copied to table A.

    John

  • November 17, 2011 at 6:50 am

    #1409158

    There is no way that records are not getting inserted into other table

    Reasons would be

    1. May be your appllying some where caluse that may reduse some records

    2.May be Table A have PK and tableB wont and there may be duplicate reocrds in TableB which not got into tableA

    3. As some one said above may be some trigger is hitting at background

    4. my request check the no of records agin in Table to see it has 100 or 90

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • November 18, 2011 at 7:39 am

    #1409922

    "No other information about the table (DDL) is provided." Why? Don't you have access to the table to determine the structure (as Sami hinted at)? Is this a real issue or an interview question to see if you can come up with the possible scenarios yourself?

  • November 18, 2011 at 12:52 pm

    #1410161

    It was an interview question. And no other information was provided.

  • November 18, 2011 at 1:26 pm

    #1410177

    I would suggest on issuing: DBCC checktable for that specific table and see if there are any rows that are corrupt that needs to be corrected.

    thanks,

    Fitsum

  • November 18, 2011 at 7:05 pm

    #1410258

    Walton (11/18/2011)

    It was an interview question. And no other information was provided.

    I'm not going to give you the answer but I will tell you where to look for the answer. Lookup "Create Index" in Books online and look for "<relational_index_option>" without the quotes. The answer you seek is in the options that follow that.

    --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)
    Intro to Tally Tables and Functions

  • November 18, 2011 at 7:06 pm

    #1410259

    sami.sqldba (11/17/2011)

    There is no way that records are not getting inserted into other table.

    Read my post immediately above this one. You'll soon change your mind. 😉

    --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)
    Intro to Tally Tables and Functions

  • November 18, 2011 at 7:07 pm

    #1410260

    John Mitchell-245523 (11/17/2011)

    The only thing I can think of is that a trigger on table A is deleting some of the rows, or modifying what is copied to table A.

    John

    Lookup the index options I pointed to. 😉

    --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)
    Intro to Tally Tables and Functions

  • November 18, 2011 at 7:08 pm

    #1410261

    Dev (11/16/2011)

    It's not possible.

    Ladies and Gentlemen, please place your bets. 😉

    --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)
    Intro to Tally Tables and Functions

  • November 19, 2011 at 3:00 am

    #1410285

    Jeff Moden (11/18/2011)

    Walton (11/18/2011)

    It was an interview question. And no other information was provided.

    I'm not going to give you the answer but I will tell you where to look for the answer. Lookup "Create Index" in Books online and look for "<relational_index_option>" without the quotes. The answer you seek is in the options that follow that.

    Jeff, are you pointing to this?

    | IGNORE_DUP_KEY = { ON | OFF }

  • November 19, 2011 at 3:06 am

    #1410286

    Jeff Moden (11/18/2011)

    Dev (11/16/2011)

    It's not possible.

    Ladies and Gentlemen, please place your bets. 😉

    Yes, please :-D. The good point here is "the DDL is missing". So you may take any assumption for it.

    I believe you assumed it could be because of Unique Index (Ignore Duplicates). My assumption here is it's a simple table without any key / constraints.

  • November 19, 2011 at 5:56 am

    #1410298

    Jeff Moden (11/18/2011)

    Walton (11/18/2011)

    It was an interview question. And no other information was provided.

    I'm not going to give you the answer but I will tell you where to look for the answer. Lookup "Create Index" in Books online and look for "<relational_index_option>" without the quotes. The answer you seek is in the options that follow that.

    My bet, the interviewer was looking for something far, far simpler than that, something that I keep seeing that does just this kind of thing.

    For example, I saw a table once that, no matter what you inserted into it, it was always empty. That was fun.

    For the record, this is a stupid interview question. It's another 'read my mind' kind of question that generally serves just to make the interviewer feel superior. With no DDL given, you cannot safely assume anything about the table, the indexes on it or any other related structures.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2011 at 12:09 am

    #1410374

    GilaMonster (11/19/2011)

    For the record, this is a stupid interview question. It's another 'read my mind' kind of question that generally serves just to make the interviewer feel superior. With no DDL given, you cannot safely assume anything about the table, the indexes on it or any other related structures.

    I don't see it that way at all. I think it's one of those "thought provoking/discuss it with me" questions designed to instill the fact that you "cannot safely assume anything about the table" and that the interviewer was looking for the interviewee to bring up such "possibilities" as indexes and triggers because most people don't even think about such things. 🙂 How many times have you and I answered "odd" forum questions where the correct answer was "Is there a trigger on the table causing this"?

    --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)
    Intro to Tally Tables and Functions

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

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