1

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?

Mercer Morris
  • 11
  • 1
  • 2
  • Put \n inside the ROWDELIMINATOR. Check out: https://stackoverflow.com/questions/39327089/sql-server-bulk-insert-into-temporary-table-syntax-error/39328075 – pPat Jul 09 '18 at 15:18

0 Answers0