在Insert ... Values上使用Scope_Identity链接表

I am writing a query to insert values from excel to sql server. I have a table with an Identity(1,1) field and I want this to link to another table.

我正在编写一个查询，以便将值从excel插入到sql server。我有一个具有Identity（1,1）字段的表，我希望它链接到另一个表。

Example:

例：

ID      Vegetables
1       Corn/Peas
2       Beans/Spinach

Want to link to a table that looks like:

想链接到一个看起来像这样的表：

ID      Vegetable
1       Corn
1       Peas
2       Beans
3       Spinach

I have a coded split string function that takes a string and delimiter and splits it, returning a table.

我有一个编码的分割字符串函数，它接受一个字符串和分隔符并将其拆分，返回一个表。

I want to use this on the insert statement and I have a variable

我想在insert语句中使用它，我有一个变量

@scopeID = SCOPE_Identity()

I think I need to use cross apply but not sure because I am essentially adding to both tables.

我想我需要使用交叉应用但不确定，因为我实际上是添加到两个表。

I have something like:

我有类似的东西：

Insert Into [VegetableTable](
[Vegetable])

Values(
@Vegetable --pulling from Excel VBA
)

Declare @scopeID Int
Set @scopeID = SCOPE_IDENTITY()

any help on writing the way to get table 2 would be really helpful.

任何有关写出获得表2的方法的帮助都会非常有帮助。

Thanks

谢谢

Edit: I have a split string that returns something like:

编辑：我有一个拆分字符串，返回如下内容：

ID           Vegetable             Want (ScopeID)
1            Corn                      1
2            Peas                      1
3            Broccoli                  1
1           Beans                      2
2          Spinach                     2

If I added broccoli to the first list. But I need to match these up so my scope identity pairs with the vegetable, in this case 2 separate strings.

如果我将西兰花添加到第一个列表中。但我需要匹配这些，所以我的范围标识与蔬菜配对，在这种情况下是2个单独的字符串。

2 个解决方案

#1

You can use this query if you have fixed number of delimiters.

如果您有固定数量的分隔符，则可以使用此查询。

;with cte as (
select *, substring(vegetables, 0,charindex('/', vegetables)) firsthalf, substring(vegetables, charindex('/', vegetables)+1, len(vegetables)) secondhalf from veg 
) select id, firsthalf from cte union all
select id,secondhalf from cte

#2

If I understand the question correctly, then I would simply create an initial excel import table with a trigger defined on the table. The import table will generate your ID and the trigger on the import table can then transform the data into the format you want. Below an example of the table import table:

如果我正确理解了这个问题，那么我只需创建一个初始的excel导入表，并在表上定义一个触发器。导入表将生成您的ID，然后导入表上的触发器可以将数据转换为您想要的格式。下面是表导入表的示例：

CREATE TABLE [dbo].[VegetableTableExcel](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Vegetables] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_VegetableTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Next the result table:

接下来是结果表：

CREATE TABLE [dbo].[Vegetables](
    [ID] [int] NOT NULL,
    [VegetableName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Finally, the trigger in the original import that transforms your source import from excel:

最后，原始导入中的触发器将从excel转换源导入：

CREATE TRIGGER SplitVegetable ON VegetableTableExcel
AFTER INSERT
AS


    MERGE INTO Vegetables AS [Target]
    USING   (

                SELECT  [Source].ID
                        ,Vegetables.VegetableName
                FROM    inserted [Source]
                CROSS APPLY (
                                SELECT  splitdata AS VegetableName
                                FROM    dbo.SplitString([Source].Vegetables, '/')
                            ) Vegetables        
            ) AS [Source]
    ON ([Target].[ID] = [Source].[ID])  AND ([Target].[VegetableName] = [Source].[VegetableName])
    WHEN NOT MATCHED BY TARGET THEN  
    INSERT  (
                 [ID]
                ,[VegetableName]
            ) VALUES 
            (
                [Source].[ID] 
                ,[Source].[VegetableName]
            ) ;

For completeness I have included the splitter function used to split the string containing the vegetable names below:

为了完整性，我已经包含了用于拆分包含以下蔬菜名称的字符串的拆分器功能：

CREATE FUNCTION [dbo].[SplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

#1