Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DenizD
Starting Member
5 Posts |
 Posted - 2006-11-22 : 11:30:09
|
Hello! Consider you have the Table "Maps" with the following columns and datatypes: Map varbinary(MAX) MD5 nchar(100) Date datetime ZipFileName nchar(100) I wanted to insert a large Zip file into this table (422MB). I used this statement: INSERT INTO [mydbdd].[dbo].[Maps] (Map) SELECT * FROM OPENROWSET ( BULK 'D:\Maps\29_30_6.zip', SINGLE_BLOB) AS TEMP As you can see, I only write in the colum "Map" in the "Maps" table. My question is: What must the statement look like, to insert Data into the columns MD5,Date and ZipFileName together with the Map (BLOB) in one statement or transaction. If I tried one statment I got the error message that no subset is allowed. My other question is: I used the statement above to insert small data (<1MB). If I inserted the zip file (=422MB) and tried a select on the table in the managment studio, I only got a table that said the columns where "read only" (although I only wrote in the column "Map", the other columns had this read only flag to). How to read a table with so much data? I would have tried a SELECT with a WHERE clause, but this leads to the problem above (inserting all columns in one statement). I am looking forward to your answers. Deniz |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-22 : 11:49:24
|
422 Mb of data??? please don't do that.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-22 : 12:16:49
|
why not just keep the zip on the file system and store the path to it in sql?
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
DenizD
Starting Member
5 Posts |
Posted - 2006-11-22 : 12:23:12
|
| Yes, that is possible. But what if you don't want or can't use this problem solution?Maybe you know the Type BFILE from Oracle Databases.BFILE (External Binary File) is a binary file stored outside of the database in the host operating system file system, but accessible from database tables.What is the BFILE equivalent on a Microsoft SQL Server? I could not find one in the MSDN or Documentation - so maybe there is none? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-22 : 12:32:13
|
sql server doesn't have such a type. I don't understand why you wouldn't or couldn't use that solution though. sql server is not the place to be storing huge binary files. that's what the file system is for.
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
DenizD
Starting Member
5 Posts |
Posted - 2006-11-23 : 04:49:46
|
| Thank you for the answers and suggestions.What about the SQL Statement? I am still trying to put the data into the DB in a single statement. |
|
|
|
|
|
|
|
|
BLOB Insert and Select of BIG Data