CODEWITHSUNDEEP
sqlsql-server
Glowie
Glowie

Reputation: 2309

Lines of text as SQL Server 2008 statement input

I have lines of IP addresses and I would like to input it into 

SELECT 
    [IP_ADDR1_TEXT],
    CASE 
        WHEN EXISTS (SELECT [IP_ADDR1_TEXT]
                     FROM [dbo].[V_SEM_COMPUTER]
                     WHERE [IP_ADDR1_TEXT] = 'line from c:\ip_list.txt') 
          THEN CAST(1 AS BIT)
         ELSE CAST(0 AS BIT) 
    END
FROM 
    [dbo].[V_SEM_COMPUTER]
WHERE 
    [IP_ADDR1_TEXT] = 'line from c:\ip_list.txt'

Where the output looks like 

IP Address    Exists
10.10.10.10    1
10.10.10.11    0
10.10.10.12    1
...

I am testing with 

SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\ip_list.txt', SINGLE_NCLOB) MyFile

And it displays all the input in a single cell.

Please point me in the right direction.

Upvotes: 0

Views: 48

Answers (1)

SubqueryCrunch
SubqueryCrunch

Reputation: 1495

DECLARE @Variable NVARCHAR(MAX)
CREATE TABLE ##TestTable (value NVARCHAR(100))
SELECT @Variable=BulkColumn 
FROM OPENROWSET (BULK 'c:\test.txt', SINGLE_CLOB) MyFile
SELECT @Variable = 'INSERT INTO ##TestTable VALUES(''' + REPLACE(@Variable,CHAR(10),'''); INSERT INTO ##TestTable VALUES(''')+''');'
EXEC( @Variable)
PRINT @Variable
SELECT * FROM ##TestTable
DROP TABLE ##TestTable

This is how i did it :)

Upvotes: 1

Related Questions