I am working on SQL Server 2008 to create a stored procedure that:
'1,2,3'What I am trying to do is something like this:
WHILE (select vlaue FROM dbo.SplitString('1,2,3',',')) has rows
insert into TableName (col1,col2) values (col1Data, value)
I am having a hard time trying to find the right syntax for this.
I use this Table-valued function:
CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
Which takes a string with a separator and returns a table with two columns the first returns a 1-based position and the second the element at that position in the string:
Usage:
SELECT * FROM dbo.Split(',', '1,2,3')
Returns:
pn s
1 1
2 2
3 3
To Insert results into a table:
INSERT INTO TableName (Col1)
SELECT S FROM dbo.Split(',', '1,2,3)
For your specific example change your syntax to be:
insert into TableName (col1,col2)
select col1Data, value FROM dbo.SplitString('1,2,3',',')
The typical INSERT INTO ... SELECT ... should do:
INSERT INTO TableName (col1,col2)
SELECT @col1Data,value FROM dbo.SplitString('1,2,3',','))
If someone else is looking for this, I was about to make a split function as several answers mentioned but noticed there's a built-in function that does this already.
string_split was added in MSSQL 2016.
INSERT INTO Project.FormDropdownAnswers (FkTableId, CreatedBy, CreatedDate)
SELECT 123, TRY_CAST(value AS INT), @username, getdate()
FROM string_split('44,45,46,47,55',',')
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql
CREATE TABLE tablename
(
id SMALLINT ,
value INT
)
INSERT INTO tablename ( id, value )
SELECT * FROM dbo.Split('1,2,3',',')
try this....
If need to use as variables there is 2 nice options:
Procedure MF_SPLIT
CREATE PROC [MF_SPLIT] (@ELS NVARCHAR(MAX)=NULL OUTPUT, @RET NVARCHAR(MAX)=NULL OUTPUT, @PROC NVARCHAR(MAX)=NULL) AS BEGIN
IF @ELS IS NULL BEGIN
PRINT ' @ELS
List of elements in string (OUTPUT)
@RET
Next return (OUTPUT)
@PROC
NULL = '','', content to do split
Example:
DECLARE @NAMES VARCHAR(100) = ''ERICK,DE,VATHAIRE''
DECLARE @N VARCHAR(100)
WHILE @NAMES IS NOT NULL BEGIN
EXEC MF_SPLIT @NAMES OUTPUT, @N OUTPUT
SELECT List = @NAMES, ActiveWord = @N
END'
RETURN
END
SET @PROC = ISNULL(@PROC, ',')
IF CHARINDEX(@PROC, @ELS) = 0 BEGIN
SELECT @RET = @ELS, @ELS = NULL
RETURN
END
SELECT
@RET = LEFT(@ELS, CHARINDEX(@PROC, @ELS) - 1)
, @ELS = STUFF(@ELS, 1, LEN(@RET) + 1, '')
END
Usage:
DECLARE @NAMES VARCHAR(100) = '1,2,3'
DECLARE @N VARCHAR(100)
WHILE @NAMES IS NOT NULL BEGIN
EXEC MF_SPLIT @NAMES OUTPUT, @N OUTPUT
SELECT List = @NAMES, ActiveWord = @N
END
Procedure MF_SPLIT_DO (Depends of MF_SPLIT), less sintax to use BUT the code will be in a string and use default variable "@X"
CREATE PROC MF_SPLIT_DO (@ARR NVARCHAR(MAX), @DO NVARCHAR(MAX)) AS BEGIN
--Less sintax
DECLARE @X NVARCHAR(MAX)
WHILE @ARR IS NOT NULL BEGIN
EXEC MF_SPLIT @ARR OUT, @X OUT
EXEC SP_EXECUTESQL @DO, N'@X NVARCHAR(MAX)', @X
END
END
Usage:
EXEC MF_SPLIT_DO '1,2,3', 'SELECT @X'
If you have SQL Server 2016 or above and the compatibility level is 130 or above you can use the STRING_SPLIT table-valued function
INSERT INTO tableName (col1, col2)
SELECT *
FROM dbo.STRING_SPLIT('ANY TEXT', ' ', 1);
The output includes an ordinal when the optional parameter '1' is included
-- view compatibility level
SELECT compatibility_level
FROM sys.databases WHERE name = 'tableName';
GO
-- update compatibility level
ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 150;
GO
Reference:
