Insert multiple rows into temp table with a single command in SQL2005

Question

I have 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 the temp table as follows:

 CREATE TABLE #TEMP ( Node int ) 

So I can use it in comparison with the data in another table.

The data above are separate rows in the "Node" column.

Is there an easy way to insert this data into a single command?

In addition, the data will arrive, as you see, as a string ... so I need to be able to simply concatenate it into an SQL query string. I can obviously change it if necessary.

Answer 1

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 

Answer 2

You can create a query dynamically as follows:

 declare @sql varchar(1000) set @sql = 'insert into #TEMP select ' + replace(@values, ',', ' union all select ') exec @sql 

As always, when creating queries dynamically, you must be careful to use only trusted data.

Answer 3

I would create a function that will return a table variable and then attach this function to the selection

Using:

 select * from myTable a inner join dbo.buildTableFromCSV('1,2,3') on a.id = b.theData 

Here is my function for 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