根据另一张表插入行 [英] Insert rows according to another table

问题描述

大家好，
我的问题是这个.我有一个数据库，可以支持本地化了.每个实体的所有可本地化数据都保存在单独的表中.

示例:

表"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

这篇关于根据另一张表插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！