INSERT says its worked but hasn't!

Transact-SQL
1

Hello. Wihtout an error message I'm a little stuck here. I've used the 'script table as > insert' to generate a really simple insert statment. Typed in the data to be inserted.

I click execute, and it says '1 row(s) affected'. Great, its worked. BUUUTTT I go to the table and my new record isn't there.

When I type the exact same data into the table directly it works.

Any suggestions on what is stopping the insert statment working?

2

Please post the little script that you used so we can have a look at it.

Only other thought is that a TRIGGER rolled back your INSERT. Or it was in a Transaction Block and is waiting for COMMIT (but if you did SELECT in the same session as the INSERT I would expect you to be able to see it)

3

Do you have implicit transactions set ON? if so, you need to commit your changes after insert.

4

Thanks both.. I checked and there isn't any triggers on that table.

Tried setting implicit transactions on and off but same for both.

The code is pretty simple:

INSERT INTO [dbo].[th]
([th_actcode]
,[th_actcst1]
,[th_actcst2]
,[th_actcst3]
.....
)
VALUES
( ' '
,0.00
,0.00
,0.00
......
)

5

UNIQUE INDEX or Primary Key on some column(s)?

I expect you are just not seeing the error message for some reason.

6

I would do something like:

SELECT TOP 100 * FROM [dbo].[th] WHERE [th_actcode] = ' '    -- Show any pre-existing matches
--
INSERT INTO [dbo].[th]
           ([th_actcode]
           ,[th_actcst1]
           ,[th_actcst2]
           ,[th_actcst3]
          .....
           )
     VALUES
           ( ' '
           ,0.00
           ,0.00
           ,0.00
          ......
         )
--
SELECT TOP 100 * FROM [dbo].[th] WHERE [th_actcode] = ' '    -- Display the new row
7

There is primary key - the default value is set to 'newid()' so i've not included it in the insert statement.

I tried including it, with the value newid() but it didn't help

8

OK, its not that then ...

Try displaying any matching rows before / after. If you have some sort of transaction going on you should see the new row in the AFTER. (Run all three statements - the SELECT, INSERT and SELECT - as a single batch)

9

Cracked it! Thanks for all your help Kristen!

Running a SELECT, INSERT and SELECT in the same statement showed me it had been inserting records all long.

The issue was it was inserting one field as a INT and trimming off the leading zeros.

Today has taught me some valuable lessons :smile:

1 Like