Member 8478234 Ответов: 2

Как вставить несколько значений вместе в таблицу

Пожалуйста, найдите ниже переменные.
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'


Моя структура таблицы такова, и я хочу быть вставленным, как показано ниже

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


Если уже есть значение, то нет необходимости вставлять его снова

Что я уже пробовал:

Попробовал что-то.. но не в состоянии получить правильно

2 Ответов

Рейтинг:
2

Maciej Los

Лучшим решением для таких требований является использование обобщенное табличное выражение[^] (CTE) для разделения данных по запятым на строки, а затем для использования ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ[^] для объединения всех результирующих наборов, возвращаемых CTE.
Попробовать это:

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]


Возвращает результаты, как и ожидалось. Для получения более подробной информации, пожалуйста, смотрите:
С common_table_expression (Transact-SQL)[^]
Использование Общих Табличных Выражений[^]
Рекурсивные Запросы, Использующие Обобщенные Табличные Выражения[^]
Визуальное представление SQL-соединений[^]

Рейтинг:
1

Naga Sindhura

Привет,

Я создал одну многоразовую функцию для разбиения даты на таблицу, и имя этой функции- "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 имеет нулевые данные, то следующий запрос может не привести к каким-либо выводам. В этом случае мы можем пойти с "OUTER APPLY" вместо "CROSS join". Однако "CROSS join "имеет большую производительность, чем"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