Inserting into a temp table from a csv variable

Question

I am using SQLDOM to retrieve stock prices from a website and the response is in a variable. The response is in a CSV Format.

I can bulk insert from the CSV into a physical file into the table but I need to insert from the memory varaible without saving the file to the hard drive.

How can I bulk insert into this table without having to save the content off? I have to skip the data header row.

Current Step 1

Using SQL:DOM I obtain the data in a memory variable called @HTML

EXEC master.[sdom].[sputilGetHTTP] 
@URL = @URL_Build,
@ResponseText = @HTML OUTPUT,
@ContentType = 'text/csv',
@SuppressResultset = 1

The data is formatted as csv with a ' ' to denote the LF & CR

Date,Open,High,Low,Close,Volume 13-Apr-17,6.61,6.61,6.11,6.36,316044 12-Apr-17,6.54,6.78,6.25,6.50,561519 11-Apr-17

Current Step 2

I save the file manually to a csv file on the hard drive

Current Step 3

I import the data into a temp table that I can access for my needs

Create table #List
(
 [Date] varchar(15),
 [Open] varchar(15),
 [High] varchar(15),
 [Low] varchar(15),
 [Close] varchar(15),
 [Volume] varchar(15),
)

---

bulk insert #List
FROM 'C:\Download\Import.csv'
 WITH
 (
 FIELDTERMINATOR = ',',
 FIRSTROW = 2,
 ROWTERMINATOR = ' '
 )

---

Needed Solution

Instead of copying the contents of the memory variable to the hard drive to import it as a CSV skipping the header row. Is there a way to use a memory variable as the csv file in the bulk insert statement skipping the header row?

• pPat: Put \n inside the ROWDELIMINATOR. Check out: stackoverflow.com/questions/39327089/…