如何在表中一起插入多个值 [英] How do insert multiple values all together in a table

问题描述

请在下面找到变量。

Please find below the variables.

Declare @User NVARCHAR(500) = '108,124', 
    @Role INT = 5,  
    @cate INT = 1,        
    @Type INT = 3,
    @Item NVARCHAR(500) = '3,4,5',
    @BU NVARCHAR(500) = '57,58,31'

我的表结构是这样的，我想插入如下

My table structure is like this and i want to be insert as below

user cate Type Item BU  role
108   1    3    3   57    5
108   1    3    4   57    5
108   1    3    5   57    5
108   1    3    3   58    5
108   1    3    4   58    5
108   1    3    5   58    5
108   1    3    3   31    5
108   1    3    4   31    5
108   1    3    5   31    5
128  1    3    3   57    5
128    1    3    4   57    5
128    1    3    5   57    5
128    1    3    3   58    5
128    1    3    4   58    5
128    1    3    5   58    5
128    1    3    3   31    5
128    1    3    4   31    5
128    1    3    5   31    5

如果已有价值则无需再次插入



我是什么尝试过：



曾经尝试过的东西......但是无法正确使用

If already value is there no need to insert it again

What I have tried:

Had tried something..but not able to get correctly

推荐答案

满足此类要求的最佳解决方案是使用公用表格表达式 [ ^ ]（CTE）将逗号上的数据拆分成行然后使用 CROSS JOIN [ ^ ]加入CTE返回的所有结果集。

试试这个：< br $>

The best solution for such of requirements is to use Common Table Expressions[^] (CTE) to split data on comma into rows and then to use CROSS JOIN[^] to join all resultsets returned by CTE's.
Try this:

Declare @User NVARCHAR(500) = '108,124', 
    @Role INT = 5,  
    @cate INT = 1,        
    @Type INT = 3,
    @Item NVARCHAR(500) = '3,4,5',
    @BU NVARCHAR(500) = '57,58,31'


;WITH Users AS
(
	SELECT CONVERT(INT, LEFT(@user, CHARINDEX(',', @User)-1)) AS U, RIGHT(@user, LEN(@User) - CHARINDEX(',', @User)) AS Remainder
	WHERE CHARINDEX(',', @User)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS U, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Users
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS U, NULL AS Remainder
	FROM Users
	WHERE CHARINDEX(',', Remainder)=0
), Items AS
(
	SELECT CONVERT(INT, LEFT(@Item, CHARINDEX(',', @Item)-1)) AS I, RIGHT(@Item, LEN(@Item) - CHARINDEX(',', @Item)) AS Remainder
	WHERE CHARINDEX(',', @Item)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS I, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Items
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS I, NULL AS Remainder
	FROM Items
	WHERE CHARINDEX(',', Remainder)=0
), BUs AS
(
	SELECT CONVERT(INT, LEFT(@BU, CHARINDEX(',', @BU)-1)) AS B, RIGHT(@BU, LEN(@BU) - CHARINDEX(',', @BU)) AS Remainder
	WHERE CHARINDEX(',', @BU)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS B, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM BUs
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS B, NULL AS Remainder
	FROM BUs
	WHERE CHARINDEX(',', Remainder)=0
)
--INSERT INTO YourTableNameHere ([User], Cate, [Type], Item, BU, Role)
SELECT u.U AS [User], @cate As Cate, @Type AS [Type], i.I AS Item, b.B AS BU, @Role AS Role
FROM Users AS u, Items As i, BUs As b
ORDER BY [User]

按预期返回结果。有关详细信息，请参阅：

WITH common_table_expression（Transact-SQL） [ ^ ]

使用公用表格式 [ ^ ]

使用公用表格表达式的递归查询 [ ^ ]

SQL连接的可视化表示 [ ^ ]

Returns results as expected. For further details, please see:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
Visual Representation of SQL Joins[^]

我创建了一个可重用的函数，用于将日期拆分为表，函数名称为split。

Hi,

I have created one reusable function for splitting the date into a table and the function name is "split".

CREATE FUNCTION split (
@delimitedString VARCHAR(1000),
@delimitedFormat VARCHAR(10) 
)
RETURNS  @a TABLE(Id smallint IDENTITY(1,1),
value VARCHAR(100)
)

AS
BEGIN 

DECLARE @charIndex smallInt =0 
DECLARE @charlength smallInt

WHILE LEN(@delimitedString) > 0
BEGIN 
	SET @charIndex = CHARINDEX(@delimitedFormat,@delimitedString)
	SET @charlength = 0 
	IF @charIndex = 0 
		Begin
			INSERT INTO @a(value)
			SELECT  @delimitedString
			
			RETURN 
		END
	ELSE 
		BEGIN 
			INSERT INTO @a(value)
			SELECT  SUBSTRING(@delimitedString,1,@charIndex-1)
			
			SET @charlength = @charlength + @charIndex 
			SET @delimitedString = SUBSTRING(@delimitedString,@charlength+1,LEN(@delimitedString)-@charIndex + 1)
			
		END	
	END		
RETURN
END

我用过@ User，@ Item，@ BU变量的相同功能，它产生一个表。并使用CROSS join加入这些表。如果像@Item这样的变量具有Null数据，则以下查询可能不会产生任何输出。在这种情况下，我们可以使用OUTER APPLY代替CROSS join。但是，CROSS join比OUTER APPLY具有更高的性能。

I have used same function for "@User", "@Item","@BU" variables and it results a table. And joined these tables using CROSS join. If a variable like @Item is having Null data then the following query might not result any output. In this case we can go with the "OUTER APPLY" instead "CROSS join". However, "CROSS join" has more performance than the "OUTER APPLY".

Declare @User NVARCHAR(500) = '108,124', 
    @Role INT = 5,  
    @cate INT = 1,        
    @Type INT = 3,
    @Item NVARCHAR(500) = '3,4,5',
    @BU NVARCHAR(500) = '57,58,31'

SELECT u.value AS 'user',cate,Type,i.value AS 'Item',bu.value AS 'BU',Role 
from split(@User,',')  u
CROSS join (SELEcT   @cate AS cate ) cate
CROSS join (SELEcT   @Type AS Type ) Type
CROSS join split(@Item,',') i
CROSS join split(@BU,',') bu
CROSS join (SELEcT   @Role AS Role ) Role
ORDER BY u.value

这篇关于如何在表中一起插入多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！