SQL Statement for Inserting

Question

4.00/5 (2 votes)

See more:

SQL-Server-2005

, +

Hi all,
I have 2 semicolon seperated string passed to a stored procedure along with other variables with simple values.
These semicolon seperated values actually needs to be split and inserted as different rows.
Both the semicolon seperated values are not interrelated and hence can be inserted together into 2 different fields in any order.The spliting is done by using

SQL

select CAST(DATA AS INT) as id  from SplitString('30:40:50',':')

but i want to combine both these splits such that i could pass them together into single insert statements.

for example,
I have string1 '30:40:50' and string2 '23,58,48,60' and some values @id=101,datecreated...

Now i want to insert this values into a table that would look like this,

101 30 23 Jun 25 2013 12:22PM
101 40 58 Jun 25 2013 12:22PM
101 50 48 Jun 25 2013 12:22PM
101 null 60 Jun 25 2013 12:22PM

Any of the string could be longer or smaller or equal.

I am unable to figure out proper way of doing this

Thanks in advance

Posted 25-Jun-13 2:29am

shivprasads

Add a Solution

3 solutions

Solution 1

Hi,

Consider your string as,

string1 '30:40:50' and string2 '23,58,48,60,70'

Pass the string value like string1 '30:40:50::60' and string2 '23,58,48,60,82'
When splitting it will take corresponding value without confusion.

Posted 25-Jun-13 2:47am

Karthik J, Coimbatore

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)

Radhadeep12 · Accepted Answer · 2013-06-25T04:32:00

May this helps

CREATE TABLE MainTable(Id INT,StringVal1 INT,StringVal2 INT,DateVal DATE)
GO

CREATE PROCEDURE uspTest
        @Id INT,
	@String1 VARCHAR(MAX),
	@SplitChar1 CHAR,
	@String2 VARCHAR(MAX),
	@SplitChar2 CHAR,
	@DateVal DATE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @XmlString XML
    
	CREATE TABLE #Value(Id INT IDENTITY,Val INT)
	SELECT @XmlString = 
	CONVERT(xml,'<root><s>' + REPLACE(@String1,@SplitChar1,'</s><s>') + '</s></root>')
	
	INSERT #Value(Val)
	SELECT A.Value FROM
	(
	SELECT [Value] = T.c.value('.','VARCHAR(20)') FROM @XmlString.nodes('/root/s') T(c)
	)A
	
	CREATE TABLE #Value1(Id1 INT IDENTITY,Val1 INT)
	SELECT @XmlString = 
	CONVERT(xml,'<root><s>' + REPLACE(@String2,@SplitChar2,'</s><s>') + '</s></root>')
    
        INSERT #Value1(Val1)
	SELECT A.Value FROM
	(
	SELECT [Value] = T.c.value('.','VARCHAR(20)') FROM @XmlString.nodes('/root/s') T(c)
	)A 
	
	SELECT * INTO #NewTab
	FROM
	(
	  SELECT V.Val,V1.Val1 FROM #Value V FULL OUTER JOIN #Value1 V1 ON V1.Id1 = V.Id
	)A

	INSERT MainTable(StringVal1,StringVal2)
	SELECT * FROM #NewTab
	
	UPDATE MainTable SET Id = @Id
	UPDATE MainTable SET DateVal = @DateVal	
		
	DROP TABLE #NewTab
	DROP TABLE #Value
        DROP TABLE #Value1
END
GO

EXEC uspTest 101,'30:40:50',':','23,58,48,60',',','2013-06-25'
GO  
SELECT * FROM MainTable

Id	StringVal1	StringVal2	DateVal
101	30		23		2013-06-25
101	40		58		2013-06-25
101	50		48		2013-06-25
101	NULL		60		2013-06-25

Maciej Los · Accepted Answer · 2013-06-26T09:28:00

Another solution is to use Common Table Expressions[^].

Have a look here:

SQL

DECLARE @string1 VARCHAR(30)
DECLARE @string2 VARCHAR(30)
DECLARE @id INT
DECLARE @curDate DATETIME

SET @string1 ='30:40:50' 
SET @string2 ='23,58,48,60'
SET @id = 101
SET @curDate = GETDATE()

--destination table
DECLARE @tmp TABLE (ID INT, Val1 INT, Val2 INT, aDate DATETIME)

--splited values of string1
DECLARE @fv TABLE (ID INT, Val1 INT, aDate DATETIME, RowNo INT)
;WITH firstVal AS 
(
	SELECT @id AS ID, LEFT(@string1, CHARINDEX(':',@string1)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(@string1, LEN(@string1)-CHARINDEX(':',@string1))) AS Remainder, 1 AS RowNo
	WHERE CHARINDEX(':',@string1)>0
	UNION ALL
	SELECT @id AS ID, LEFT(Remainder, CHARINDEX(':',Remainder)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(Remainder, LEN(Remainder)-CHARINDEX(':',Remainder))) AS Remainder, RowNo + 1 AS RowNo
	FROM firstVal
	WHERE CHARINDEX(':',Remainder)>0
	UNION ALL
	SELECT @id AS ID, Remainder AS Val1, NULL AS Remainder, RowNo + 1 AS RowNo
	FROM firstVal
	WHERE CHARINDEX(':',Remainder)=0
)
INSERT INTO @fv (ID, Val1, aDate, RowNo)
SELECT ID, Val1, @curDate AS aDate, RowNo
FROM firstVal 

--splited values of string2
DECLARE @sv TABLE (ID INT, Val2 INT, aDate DATETIME, RowNo INT)
;WITH secondVal AS
(
	SELECT @id AS ID, LEFT(@string2, CHARINDEX(',',@string2)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(@string2, LEN(@string2)-CHARINDEX(',',@string2))) AS Remainder, 1 AS RowNo
	WHERE CHARINDEX(',',@string2)>0
	UNION ALL
	SELECT @id AS ID, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',',Remainder))) AS Remainder, RowNo +1 AS RowNo
	FROM secondVal
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT @id AS ID, Remainder AS Val2, NULL AS Remainder, RowNo + 1 AS RowNo
	FROM secondVal
	WHERE CHARINDEX(',',Remainder)=0
)
INSERT INTO @sv (ID, Val2, aDate, RowNo)
SELECT ID, Val2, @curDate AS aDate, RowNo
FROM secondVal 

--get count of splited values
DECLARE @fvc INT
DECLARE @svc INT

SELECT @fvc = COUNT(ID)
FROM @fv

SELECT @svc = COUNT(ID)
FROM @sv

--SELECT @fvc AS CountOfFirstValues, @svc AS CountOfSecondValues

IF (@fvc > @svc)
	BEGIN
		INSERT INTO @tmp (ID, Val1, Val2, aDate)
		SELECT t1.ID, t1.Val1, t2.Val2, t1.aDate
		FROM @fv AS t1 LEFT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
	END
ELSE
	BEGIN
		INSERT INTO @tmp (ID, Val1, Val2, aDate)
		SELECT t2.ID, t1.Val1, t2.Val2, t2.aDate
		FROM @fv AS t1 RIGHT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
	END

SELECT *
FROM @tmp

Result:

101   30      23    2013-06-26 21:22:02.763
101   40      58    2013-06-26 21:22:02.763
101   50      48    2013-06-26 21:22:02.763
101   NULL    60    2013-06-26 21:22:02.763

SQL Statement for Inserting

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0