Click here to Skip to main content
14,882,317 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please find below the variables.
SQL
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
Posted
Updated 19-Aug-16 4:14am
v2

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:

SQL
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]


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[^]
   
v3
Hi,

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

SQL
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


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

SQL
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'


SQL
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
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900