It is possible to bulk copy a data file as image data into Microsoft® SQL Server™. The command to load the data file Test.doc into the bitmap table in the pubs database using the bcp utility is:
bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername
bcp prompts:
Enter the file storage type of field c1 [image]:
Enter the prefix length of field c1 [4]: 0
Enter length of field c1 [4096]: 5578
Enter the field terminator [none]:
In this example, the data file will be loaded into column c1 and 5578 is the length of the data file.
Using the BULK INSERT statement, a format file needs to be created first and then used to provide the format information. To create the format file, use the bcp utility:
bcp pubs..bitmap out c:\bitmap.txt -Sservername -Usa -Ppassword
The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of bitmap. For the c1 column, the values are listed in this table.
| Prompt | Value |
|---|---|
| File storage type | image |
| Prefix length | 0 |
| Field length | 5578 |
| Field terminator | none |
The Bcp.fmt file:
7.0
1
1 SQLBINARY 0 5578 "" 1 c1
Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool such as SQL Server Query Analyzer:
BULK INSERT pubs..bitmap FROM 'c:\test.doc'
WITH (
FORMATFILE = 'c:\Bcp.fmt'
)
Note It is not possible to bulk copy data into text, ntext, and image columns that have DEFAULT values.