Insert Into - No Error - Row not in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert Into - No Error - Row not in table

Author

Topic

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-12 : 19:24:11

I ran across something new today. I have an insert statement (found below) that inserted into a table (create table found below) on a SQL 2008 standard edition server. When the insert statement is executed it returns with "1 row(s) affected", but the new record is not in the table. I found where the problem is. It is with the BNumber field as an Int and the insert has that value with single quotes around it. When I removed the single quotes it inserted as expected. Does anyone know why it did not return an error? It should have given me a message that the data types were mismatched or something like that. Any insight or previous experience would be helpful.

Insert statement:
Insert Into ManifestHistory
(ManifestTripId, BusNumber, TagNumber, BadgeNumber, ArrivalTime
, DepartureTime, Mileage, DepartureLat, DepartureLong, Arrivallat
, ArrivalLong, Cancelled)
Values
(10853947, '0000951', 'B17264', '05603', '7/31/2009 8:48:23 AM'
, '7/31/2009 8:48:23 AM', 0, 40.69471, -111.91492, 40.69471
, -111.91492, 2)

Table Creation:
CREATE TABLE [dbo].[History](
[HistoryId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TripId] [int] NOT NULL,
[DNumber] [varchar](25) NOT NULL,
[TNumber] [varchar](25) NULL,
[BNumber] [int] NULL,
[ATime] [varchar](50) NOT NULL,
[DTime] [varchar](50) NOT NULL,
[Distance] [int] NULL,
[DLat] [float] NULL,
[DLong] [float] NULL,
[ALat] [float] NOT NULL,
[ALong] [float] NOT NULL,
[Cancelled] [int] NULL,
[Processed] [bit] NULL,
[ProcessedDate] [date] NULL,
[ImportDate] [datetime] NOT NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
[HistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_DNumber] DEFAULT ((0)) FOR [DNumber]
GO

ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_ALat] DEFAULT ((0)) FOR [ALat]
GO

ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_ALong] DEFAULT ((0)) FOR [ALong]
GO

ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_ImportDate] DEFAULT (getdate()) FOR [ImportDate]
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 09:00:57

you're inserting into ManifestHistory, but gave us schema for History. Seems like you're selecting from the wrong table

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-13 : 10:56:28

No, just forgot to change the table name for this copy. It is all with History table.

Insert statement should be:
Insert Into History
(TripId, DNumber, TNumber, BNumber, ATime, DTime, Distance
, DLat, DLong, Alat, ALong, Cancelled)
Values
(10853947, '0000951', 'B17264', '05603', '7/31/2009 8:48:23 AM', '7/31/2009 8:48:23 AM', 0
, 40.69471, -111.91492, 40.69471, -111.91492, 2)

When searching for TripID of 10853947 it did not exist.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 11:54:25

is there trigger on the table?

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-13 : 13:41:25

There are no triggers on the table. I included the table creation including the defaults. I was able to get the insert to work when I removed the single quotes from the Int field, but the question is why did it not return an error when it would not insert the record.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-13 : 18:51:25

Unbelievable...
Can you show us the select statement please which you have used to check if the record was inserted?

No, you're never too old to Yak'n'Roll if you're too young to die.

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-14 : 13:33:38

select *
from History
where TripId = 10853947

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-14 : 13:35:05

It would come up with no records until I took off the single quotes from the BNumber value and ran that insert. Then it would return. The insert would not return an error, just '1 row(s) affected'.

GeneralSQL
Starting Member

10 Posts

Posted - 2009-08-14 : 13:40:16

I just learned the database was a backup and restore from 2005 to 2008. It was in 2005 compatibility mode for a few months until the end of June 2008 when it was changed to compatibility mode for 2008.

I just ran the table create script and the insert worked on another server. Could it be server settings preventing the implicit conversion from char to int?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources