When carrying out a simple insert into a table in a SQL server table the X number of records affected text is displayed, although a select * from the table immediately afterwards does not yield any results.
There isn’t necessarily any single answer to this, so look at the following areas;
- Refine your insert statement to use some hard coded values in place of a SQL select statement to remove any complication and identify if the error lies in the select statement as opposed to the insert transaction.
- If the table is a high turnover table (i.e. one that is cleared down and then repopulated with large amounts of data) check to see if there are any auto number columns that will have hit their limit (i.e. the limit of a integer etc).
- Check the database server itself to ensure there is sufficient room remaining on the drives that run both the operating system and store the database files, also check that autogrow is on.
- Attempt to rebuild any indexes on the table (this was what resolved the issue for me)
The frustrating fact with the issue above is that the “X number of records affected” following the insert leads any SQL developer to assume the records have been inserted successfully.