How do insert multiple values all together in a table

Question

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 18-Aug-16 19:48pm

Member 8478234

Updated 19-Aug-16 4:14am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2016-08-18T21:03:00

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[^]

Naga Sindhura · Answer 2 · 2016-08-19T04:14:00

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

How do insert multiple values all together in a table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0