mysql插入数据到多个表,MySQL插入数据到多个表

I am experiencing a problem in MYSQL insert.

Could any experts will answer this.

Below is my question.

Photo table :

pid - PK,

photo_src,

photo_size,

created ,

Photo_link table : - for linking more than one image in Blog table .

id ,

pid -FK

Blog Table :

bid - PK,

content,

created,

id - will have photo_link table id .

Photo :

| pid | photo_src | photo_size | created |

+-----+--------------+------------+---------------------+

| 1 | /photo/1.jpg | 400 | 2014-05-27 11:58:45 |

| 2 | /photo/2.jpg | 400 | 2014-05-27 11:58:54 |

| 3 | /photo/3.jpg | 400 | 2014-05-27 11:59:07 |

| 4 | /photo/4.jpg | 400 | 2014-05-27 11:59:14 |

+-----+--------------+------------+---------------------+

Photo_link :

+------+------+

| plid | pid |

+------+------+

| 100 | 1 |

| 100 | 2 |

| 100 | 3 |

| 101 | 2 |

| 101 | 4 |

+------+------+

Blog Table :

+-----+------------------------+---------------------+------+

| bid | content | created | pid |

+-----+------------------------+---------------------+------+

| 1 | This is my first Blog | 2014-05-27 12:04:44 | 100 |

| 2 | This is my second Blog | 2014-05-27 12:05:01 | 101 |

+-----+------------------------+---------------------+------+

Now i have to insert more than one photo into Blog , so first i have to insert the photos for new blog into photo table and again have to insert all the created photo id (pid's) in photo_link table under one id (for each blog one new id is created for photo_link table to save all photos corresponding to that blog) and insert refer that id in blog table.

My problem is how can i get all the newly created photo ids and then insert into photo_link with first time i have to create new id for new blog and then have to save all the corresponding photo with that id and then insert that photo_link id in blog also.

writing procedure may correct ? if it how to write this procedure ?

please give me some idea on this.

please help me on this.It would be great if this can be done .Thanks in advance.

解决方案

You need to SELECT SCOPE_IDENTITY after each insert of the newly created photo in order to get the Id of the inserted photo.

Then you can continue your flow based on the id you got.

Example:

DECLARE @insertedId

INSERT INTO Photo(photo_src, photo_size, created) VALUES('/photo/1.jpg', 400, GETDATE())

SELECT @insertedId = SCOPE_IDENTITY()

INSERT INTO Photo_link(content, created, pid) VALUES('This is my first Blog', GETDATE(), @insertedId)