How to insert an extended value into a table in a varbinary column?

Asked

Viewed 882 times

4

A table has a varbinary(max) column, I need it to have a 400MB input of data in this column.

How I create this data?

The Insert statement has to have the data in full or I can use a loop or a function that generates this data?

sql-server tsql varbinary

  • Of where (screen field, file) come the data and by what medium (Code) you will insert in the table?

  • I will use SQL Management Studio.

3 answers

5

You can use a function OPENROWSET, example:

CREATE TABLE Tabela(NomeArquivo nvarchar(100), Arquivo varbinary(max));
GO

INSERT INTO Tabela(NomeArquivo, Arquivo) 
   SELECT 'Texto.txt' AS NomeArquivo, * FROM OPENROWSET(BULK N'C:\Arquivo.txt', SINGLE_BLOB) AS Arquivo;
GO

Reference

4


Response from Martin Smith

INSERT INTO [YourTable] (YourFileColumn)
VALUES      ( CAST(REPLICATE(CAST(0XFF AS VARBINARY(MAX)), 
                         100 * 1024 * 1024) AS VARBINARY(MAX)))

2

Friend, although I’m not exactly answering your question the way you expect, I think putting a column up to 400Mb per record in the database doesn’t seem like a good idea.

I would only put a URL in the database and make this heavy data available somewhere on the internet or intranet accessible through the registered URL. This also allows you to distribute access to these files, migrate them, compress them and reuse them without relying on the database to do things that are not its purpose.

  • Keeping a blob like this in the comic book in general is a bad idea and can give performance problems. Best way is to save this in a file (probably already a file) and in BD save a reference to the file (link, path, etc)

Browser other questions tagged sql-server tsql varbinary

You are not signed in. Login or sign up in order to post.