用于插入的SQL语句 [英] SQL Statement for Inserting
问题描述
大家好，
我有2个分号分隔的字符串传递给存储过程以及其他具有简单值的变量。
这些分号分隔值实际上需要分割并且作为不同的行插入。
分号分隔值不相互关联，因此可以按任意顺序一起插入2个不同的字段。使用
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
select CAST(DATA AS INT) as id from SplitString('30:40:50',':')
但我希望将这两个分割结合起来，以便我可以将它们一起传递到单个插入语句中。
例如，
我有string1 '30：40：5 0'和string2 '23,58,48,60'和一些值@ id = 101，datecreated ...
现在我想将这些值插入到看起来像这样的桌子，
101   30   23   2013年6月25日下午12:22
101   40   58   2013年6月25日下午12:22
101   50   48   2013年6月25日下午12:22
101   null 60   2013年6月25日12:22
任何字符串都可能更长或更小或相等。
我无法弄清楚这样做的正确方法
提前致谢
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
推荐答案
这可以帮助
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
另一个解决方案是使用公用表格表达式 [ ^ ]。
看看这里：
Another solution is to use Common Table Expressions[^].
Have a look here:
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
考虑一下你r string as，
string1 '30：40：50'和string2 '23,58,48,60,70'
传递字符串值，如string1 '30：40：50 :: 60'和string2 '23,58,48,60,82'
分割时将采取相应的没有混淆的价值。
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.
这篇关于用于插入的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
