Insert multiple rows into temp table with one command in SQL2005

Question

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 one 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.

Answer 1

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

Answer 2

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 3

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.