将标识列值从一个表插入另一个表

本文介绍了将标识列值从一个表插入另一个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表.. ImageTable和ImageDetails ImageTable有两列...... Sno是身份，img是varBinary。我以varBinary格式在ImageTable中存储图像。 我在ImageDetails中插入细节，其中还包括一个名为HoMakerId的列，它是ImageTable的Sno的外键。所以我在ImageTable中插入图像的所有内容我也在ImageDetails中插入最后插入图像的Sno ...这样的事情 点击这里 [ ^ ]

GO ALTER procedure [dbo]。[sp_InsertImage] @ Image varbinary （max）， @ Date datetime ， @ Uploader int as 将 插入 ImageTable（img） values （ @ Image ）; 将 插入 ImageDetails（makerid，createdon，homakerid） 值（ @ Uploader ， @ Date ， （选择 top 1 sno 来自 imagetable 订单 按 sno desc ））

你认为我需要另一种方法还是这种方法没问题？我应该使用交易吗？

解决方案

使用@ScopeIdentity。像这样更改

将 插入 ImageTable（img）值（ @ Image ）; DECLARE @ Id INTEGER SET @ Id = SCOPE_IDENTITY （） 插入 进入 ImageDetails（makerid，createdon，homakerid）值（ @ Uploader ， @ Date ， @ Id ）

参考这个 http ：//technet.microsoft/en-us/library/ms190315.aspx www.c-sharpcorner/UploadFile/rohatash/identity-and-scope_identity-in-sql-server-2012/

I have two table .. ImageTable and ImageDetails ImageTable has two columns .. Sno which is identity and img which is varBinary. I am storing images in ImageTable in varBinary format. I am inserting details in ImageDetails which also include a column called HoMakerId which is a foreign key to ImageTable's Sno. So everything i am inserting image into ImageTable i am also inserting the Sno of last inserted image in ImageDetails ... something like this CLICK HERE[^]

GO ALTER procedure [dbo].[sp_InsertImage] @Image varbinary(max), @Date datetime, @Uploader int as Insert into ImageTable(img) values(@Image); Insert into ImageDetails(makerid,createdon,homakerid) values(@Uploader,@Date,(select top 1 sno from imagetable order by sno desc))

Do you think i need another approach or this one is fine? Should i be using transactions?

解决方案

Use @ScopeIdentity. Change sp like this

Insert into ImageTable(img) values(@Image); DECLARE @Id INTEGER SET @Id = SCOPE_IDENTITY() Insert into ImageDetails(makerid,createdon,homakerid) values(@Uploader,@Date,@Id)

Refer this technet.microsoft/en-us/library/ms190315.aspx www.c-sharpcorner/UploadFile/rohatash/identity-and-scope_identity-in-sql-server-2012/