根据另一张表插入行
大家好，
我的问题是这个.我有一个数据库，可以支持本地化了.每个实体的所有可本地化数据都保存在单独的表中.
示例:
表"UserGroup"具有以下列:UserGroupID，CreatedOn，ModifiedOn和Version
表"UserGroupTranslation"具有以下列:UserGroupID，LanguageID，名称，描述
(UserGroupID和LanguageID构成键)
显然还有另一个表"Language"，其中仅存储了所使用语言环境的两个字符的标签.
所以问题是这样的.我对所有表都有CRUD存储过程，但是我想将"UserGroup"和"UserGroupTranslation"表表示为外部系统的单个实体.我的想法是在SP中使用事务，可以正确处理两个表之间的关系.选择，删除和更新不是问题，但是在插入时，我的SQL知识还有些不足.
我要创建一个存储过程，该过程首先在"UserGroup"表中插入新行，然后使用新插入的ID和所有使用的语言的ID在"UserGroupTranslation"表中插入与所使用的语言一样多的行. br/>
我希望我已经足够清楚了.
任何帮助将不胜感激.
问候，Tine
Hi all,
my problem is this. I have a database, that is ready to support localization. All localizable data is kept in separate table for each entity.
Example:
Table "UserGroup" has the following columns: UserGroupID, CreatedOn, ModifiedOn and Version
Table "UserGroupTranslation" has the following columns: UserGroupID, LanguageID, Name, Description
(UserGroupID and LanguageID form the key)
There is obviously another table "Language" which stores only two-character tags for used locales.
So the question is this. I have CRUD stored procedures for all tables, but I would like to represent "UserGroup" and "UserGroupTranslation" tables as single entity to external systems. My idea is to use transactions in SP, that would properly handle relationship between the two tables. Selecting, deleting and updating is not the problem, but with inserting, well there I''m a little short with my SQL knowledge.
I want to make a stored procedure, that first inserts new row in "UserGroup" table and then uses newly inserted ID and ID''s of all used languages to insert as many rows in "UserGroupTranslation" table as there are used languages.
I hope I''ve been clear enough.
Any help would be appreciated.
Greetings, Tine
在存储过程中执行以下操作，以插入数据
步骤1:在UserGroup中插入数据
第2步:获取pke最高值的标识值，例如select @pkey = @@ identity
第3步:编写选择插入查询，如
插入UserGroupTranslation
从
中选择@ pkey，LanguageID，name，description languageuse = 1的语言
希望以上各项对您有用.
Do the following thing in your stored procedure which insert data
step 1 : insert data in UserGroup
step 2 : get identity value of pke higest value like select @pkey=@@identity
step 3 : write select inset query like
insert into UserGroupTranslation
select @pkey,LanguageID,name,description from
Language where languageuse=1
hope with the above things work for you.
非常感谢您的帮助.您提供的解决方案正是我想要的.
以下是完成此技巧的原始代码段(如果其他人有兴趣的话).
Thank you very much for your help. The solution you provided does exactly what I want.
The following is raw code snippet that does the trick, if anybody else is interested.
declare @name nvarchar(50)
declare @description nvarchar(500)
declare @id int
set @name = ''name''
set @description = ''description''
insert into UserGroup default values
set @id = SCOPE_IDENTITY()
insert into UserGroupTranslation select @id, Language.LanguageID, @name, @description from Language