Is there any way to insert data into table from a variable? Variable contents example:
123;1;500;some text here;
145;0;250;and some more text;
146;1;0;;
146;0;3;this field in previous line is empty;
- Column dividers:
; - Line dividers:
\r\n
Add a comment
|
3 Answers
If the ;-seperated contents were in a file you could use BULK INSERT. A StackOverflow question about this with solution you can find here. So dumping the contents of your variable to a file and using BULK INSERT would be one way to do it.
3 Comments
stakx - no longer contributing
I don't think that T-SQL offers the capability to do general file I/O, so dumping the variable possibly couldn't be done in the T-SQL script itself that calls the
BULK INSERT. One could just as well do everything without T-SQL then.
TT.
@stakx True, I was just thinking of a way to do it without writing a complex script. I just wonder how those ;-seperated rows came into the variable in the first place... by reading it from a file perhaps? In that case the OP can use my answer to directly bulk insert them into a table instead.
an40us
@TT. Thanks for your answer. I get this data from an application: it forms a variable and calls a stored procedure with this variable as a parameter. Looks like it is better not to pass data like this directly - I'll go for saving a file and passing path to SP and then use
BULK INSERT.A solution would be like...
declare @var varchar(100)
set @var= '123;1;500;some text here;'
select @var as variable,CHARINDEX(';',@var) as col1
,CHARINDEX(';',@var,CHARINDEX(';',@var)+1) as col2
,CHARINDEX(';',@var,CHARINDEX(';',@var,CHARINDEX(';',@var)+1)+1) as col3
,CHARINDEX(';',@var,CHARINDEX(';',@var,CHARINDEX(';',@var,CHARINDEX(';',@var)+1)+1)+1) as col4
into #temp
select * from #temp
select SUBSTRING(variable,0,col1) as Col1Text
,replace(SUBSTRING(variable,col1,col2-col1),';','') as Col2Text
,replace(SUBSTRING(variable,col2,col3-col2),';','') as Col2Text
,replace(SUBSTRING(variable,col3,col4-col3),';','') as Col2Text
from #temp
drop table #temp
Comments
DECLARE @table_var TABLE(
col_list VARCHAR(50)
)
INSERT INTO @table_var VALUES ('123;1;500;some text here');
INSERT INTO @table_var VALUES ('145;0;250;and some more text;');
INSERT INTO @table_var VALUES ('146;1;0;;');
INSERT INTO @table_var VALUES ('146;0;3;this field');
SOL 1 :
SELECT DISTINCT S.a.value('(/H/r)[1]', 'VARCHAR(25)') AS col1
, S.a.value('(/H/r)[2]', 'VARCHAR(25)') AS col2
, S.a.value('(/H/r)[3]', 'VARCHAR(25)') AS col3
, S.a.value('(/H/r)[4]', 'VARCHAR(25)') AS col4
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(col_list, ';', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM @table_var) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Sol 2:
SELECT col_list,
NewXML.value('/col_list[1]/Attribute[1]', 'varchar(25)') AS [col1],
NewXML.value('/col_list[1]/Attribute[2]', 'varchar(25)') AS [col2],
NewXML.value('/col_list[1]/Attribute[3]', 'varchar(25)') AS [col3],
NewXML.value('/col_list[1]/Attribute[4]', 'varchar(25)') AS [col4]
FROM @table_var t1
CROSS APPLY (SELECT XMLEncoded=(SELECT col_list AS [*]
FROM @table_var t2
WHERE t1.col_list = t2.[col_list]
FOR XML PATH(''))) EncodeXML
CROSS APPLY (SELECT NewXML=Cast('<col_list><Attribute>'
+ Replace(XMLEncoded, ';', '</Attribute><Attribute>')
+ '</Attribute></col_list>' AS XML)) CastXML
1 Comment
an40us
Using a table variable is not an option in my case. All the data that i work with is dynamic, and inserting it with my own hands won't work.