missing records on table

Question

missing records on table

Walton

SSCommitted

Points: 1638
More actions
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.

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

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 3

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

ACinAZ SSCommitted Points: 1849 More actions · Answer 4

"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?

Walton SSCommitted Points: 1638 More actions · Answer 5

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

fitsumkh SSChasing Mays Points: 652 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

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 }

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 12

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

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)