I've got some data in the following format:
-1,-1,-1,-1,701,-1,-1,-1,-1,-1,304,390,403,435,438,439,442,455
I need to insert it into a temp table like this:
CREATE TABLE #TEMP
(
Node int
)
So that I can use it in a comparison with data in another table.
The data above represents separate rows of the "Node" column.
Is there an easy way to insert this data, all in on开发者_如何学编程e command?
Also, the data will actually being coming in as seen, as a string... so I need to be able to just concat it into the SQL query string. I can obviously modify it first if needed.
Try something like
CREATE TABLE #TEMP
(
Node int
)
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = '-1,-1,-1,-1,701,-1,-1,-1,-1,-1,304,390,403,435,438,439,442,455 ',
@delimiter = ','
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
INSERT INTO #TEMP
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
SELECT * FROM #TEMP
DROP TABLE #TEMP
You can create a query dynamically like this:
declare @sql varchar(1000)
set @sql = 'insert into #TEMP select ' + replace(@values, ',', ' union all select ')
exec @sql
As always when creating queries dynamically, you have to be careful so that you only use trusted data.
I would create a function that would return a table variable and then join that function into the select
Use:
select * from myTable a
inner join dbo.buildTableFromCSV('1,2,3') on a.id = b.theData
Here is my function for doing this
CREATE FUNCTION [dbo].[buildTableFromCSV] ( @csvString varchar(8000) ) RETURNS @myTable TABLE (ID int identity (1,1), theData varchar(100))
AS BEGIN
DECLARE @startPos Int -- position to chop next block of chars from
DECLARE @currentPos Int -- position to current character we're examining
DECLARE @strLen Int
DECLARE @c char(1) -- current subString
-- variable initalization
-- -------------------------------------------------------------------------------------------------------------------------------------------------
SELECT @csvString = @csvString + ','
SELECT @startPos = 1
SELECT @currentPos = 1
SELECT @strLen = Len(@csvString)
-- loop over string and build temp table
-- -------------------------------------------------------------------------------------------------------------------------------------------------
WHILE @currentPos <= @strLen BEGIN
SET @c = SUBSTRING(@csvString, @currentPos, 1 )
IF ( @c = ',' ) BEGIN
IF ( @currentPos - @startPos > 0 ) BEGIN
INSERT
INTO @myTable ( theData )
VALUES ( CAST( SUBSTRING ( @csvString, @startPos, @currentPos - @startPos) AS varchar ) )
END
ELSE
begin
INSERT
INTO @myTable ( theData )
VALUES ( null )
end
SELECT @startPos = @currentPos + 1
END
SET @currentPos = @currentPos + 1
END
delete from @myTable where theData is null
return
END
精彩评论