1

I have a table in SQL Server with 3 columns:

id (int), Name (varchar), Column_Vb (Varbinary)

I want to import data into this table from a data file using BCP. The Id and Name columns contain the id and names, but the data in the Column_Vb (data type is varbinary) column contains the location of the blob files.

When I execute the BCP IN command, I want to load the blob files from the specified location into the table, but instead of that the name of the location just gets written into the table.

Is there any way through BCP that I can load these blob files into the varbinary column?

I have tried with the format file but it doesn't work. Since there is lot of data, I need a bulk importing option itself.

These screenshots show my data and format file:

Pic of the DataFile

Pic of the FormatFile

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Emilee
  • 11
  • 2
  • 2
    I don't think bcp.exe is the tool you're looking for here given that you're trying to pull in multiple file references. If it were me I'd write a PowerShell script that loads your first file using Import-Csv (set the delimiter to the `|` character) and iterates over its rows to: 1. load the referenced binary file into a byte array, then 2. insert the Id, Name and Column_Vb values as a single row. – AlwaysLearning Feb 27 '21 at 07:44
  • What is the BLOB? Is it a some kind of data lake? Do you have Azure, perhaps? – ASH Feb 27 '21 at 13:04
  • @ASH the blobs are just files, they contain binary data. It isnt anything related to data lake or azure. – Emilee Feb 27 '21 at 13:29
  • Consider doing this in C# perhaps with `SqlBulkCopy` – Charlieface Feb 27 '21 at 21:38
  • Is SqlBulkCopy supported only with C# ? If so, Is there any way to do it using java ? @Charlieface – Emilee Feb 28 '21 at 06:38
  • https://learn.microsoft.com/en-us/sql/connect/jdbc/using-bulk-copy-with-the-jdbc-driver?view=sql-server-ver15 – Charlieface Feb 28 '21 at 06:39

1 Answers1

0

I've used Azure to do this before. Without Azure, just a Guess, but maybe something like this.

CREATE TABLE [dbo].[TestBlob](
[tbId] [int] IDENTITY(1,1) NOT NULL,
[tbName] [varchar](50) NULL,
[tbDesc] [varchar](100) NULL,
[tbBin] [varbinary](max) NULL
) ON [PRIMARY]  

Insert TestBlob(tbName, tbDesc, tbBin) Select 
'81.pdf','PDF file', BulkColumn from Openrowset( Bulk 
'C:\blob\udoc\81.pdf', Single_Blob) as tb 

Insert TestBlob(tbName, tbDesc, tbBin) Select 'mountain.jpg','Image 
jpeg', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\mountain.jpg', 
Single_Blob) as tb

Insert TestBlob(tbName, tbDesc, tbBin) Select 'Questionnaire.docx','Doc 
Question', BulkColumn from Openrowset( Bulk 
'C:\blob\udoc\Questionnaire.docx', Single_Blob) as tb

Insert TestBlob(tbName, tbDesc, tbBin) Select 'txpeng542.exe','Texpad 
Exe', BulkColumn from Openrowset( Bulk 'C:\blob\udoc\txpeng542.exe', 
Single_Blob) as tb

See the link below for more info.

https://sqlrambling.net/2020/04/04/saving-and-extracting-blob-data-basic-examples/

ASH
  • 20,759
  • 19
  • 87
  • 200