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

Question

我正在编写一个从excel到sql server插入值的查询。我有一个带有Identity(1,1)字段的表，我希望它链接到另一个表。

的例子:

ID      Vegetables
1       Corn/Peas
2       Beans/Spinach

想要链接到一个表，看起来像:

ID      Vegetable
1       Corn
1       Peas
2       Beans
3       Spinach

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

我想在插入语句中使用这个我有一个变量

@scopeID = SCOPE_Identity()

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

我有这样的东西:

Insert Into [VegetableTable](
[Vegetable])
Values(
@Vegetable --pulling from Excel VBA
)
Declare @scopeID Int
Set @scopeID = SCOPE_IDENTITY()

任何关于如何获得表2的帮助都将非常有帮助。

感谢

编辑:我有一个拆分字符串，返回类似于:

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

如果我在第一个列表中添加西兰花。但是我需要匹配它们所以我的作用域标识与蔬菜配对，在这个例子中是两个单独的字符串

Answer 1

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

;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

Answer 2

如果我正确理解了这个问题，那么我将简单地创建一个初始的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]

接下来是结果表:

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

最后，原始导入中的触发器将从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]
            ) ;

为完整起见，我包含了splitter函数，用于拆分包含以下蔬菜名称的字符串:

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