0

I am using a stored procedure to insert data into two tables. But when I insert the datas the total number of rows in the first table and the second table is different, so it means that sometimes it only inserted the datas in the first table but failed to insert it in the second table. But this case should not happen in my case as the Id of the two tables is related to each other. How can I solve this problem? So that when it will insert datas in both tables or no table if an error occurs so that the number of datas are the same in both the table. My stored procedure is as follows:

Begin
insert into base_table(imgPath,store,apparelType) values (imgPath,store,apparelType);
insert into data_table(cvID,color) values
(LAST_INSERT_ID(),color);
END
Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
user1583647
  • 1,227
  • 2
  • 24
  • 48
  • http://stackoverflow.com/questions/13318924/how-do-i-insert-into-two-tables-all-at-once-in-a-stored-procedure – Nagaraj S Jan 21 '14 at 05:38
  • The stored procedure above also works to insert datas to both the table but sometimes it cannot insert the data to one table so how can I prevent this? – user1583647 Jan 21 '14 at 05:52

1 Answers1

0

To make sure that the 1st query has been successfully executed, the best way would be to add an Identity column in your base_table, then proceed as follows;

DECLARE @LAST_INSERT_ID INT
DECLARE @EXECUTION_OK char(1)
SET @EXECUTION_OK = 1

insert into base_table(imgPath,store,apparelType) values (imgPath,store,apparelType)

SELECT @LAST_INSERT_ID = SCOPE_IDENTITY()

insert into data_table(cvID,color) values (@LAST_INSERT_ID, color)
GO

If exists( Select cvID from data_table where cvID= @LAST_INSERT_ID)
Begin
@EXECUTION_OK = 0
End

SCOPE_IDENTITY: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can also use the mysql_affected_rows() function to verify that the query has been successful.

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • what if it inserts the data in the first table but cannot insert to the second table? Is there a way so that if this happens it won't insert to both of the tables? – user1583647 Jan 21 '14 at 06:19
  • According to the manual: "The MS-SQL SCOPE_IDENTITY() and the @@IDENTITY do not work in MySQL. There is also a MySQL_Insert_ID command which also can not be used in Stored procedures You have to use LAST_INSERT_ID();" -- http://dev.mysql.com/doc/internals/en/stored-procedures.html – Rick James Jan 21 '14 at 06:29
  • @user1583647 I've edited the solution to be able to check if second query has been executed correctly. You just have to check `@EXECUTION_OK` – Nadeem_MK Jan 21 '14 at 07:44
  • ok thanks....but I found another problem while inserting the datas for the data_table when the cvId reaches 7865 it stops inserting in that table..but the data is still inserted in the base_table...wherease in data_table the auto increment value is only increasing but the datas are not inserted. What could be the problem? – user1583647 Jan 22 '14 at 02:57