?
Solved

How do I make multiple insert for openrowset bulk?

Posted on 2011-09-18
6
Medium Priority
?
663 Views
Last Modified: 2012-05-12
Attach is my attempt on doing it but I get an error saying

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.

INSERT INTO [DB_X].[dbo].[SubFileContent] ([SubmissionID],[SubFileCatID],[SubFileTypeID],[FileName],[FileTitle],[SubFileContentBinary],[DateAdded],[SubFileID])
SELECT [SubmissionID]
,[SubFileCatID]
,[SubFileTypeID]
,[FileName]
,[FileTitle]
, * FROM OPENROWSET(BULK N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Temp\combined\'+[FileName], SINGLE_BLOB) AS BLOB
,[DateAdded]
,[SubFileID]
FROM [DB_X].[dbo].[SubFile]

Open in new window

0
Comment
Question by:karlomedallo23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 36558265
On this link ,http://msdn.microsoft.com/en-us/library/ms190312.aspx, you can see the specifications of OPENROWSET
You have at least 2 problems
- name must be string constant
- openrowset can only handle up to 8000 chars without using a xml definition file, your 'blob' seems to mention it will be more than that.

Sollution can be found in dynamic sql, look at http://www.sqlservercentral.com/Forums/Topic469199-5-1.aspx for an example that can work for you.
0
 
LVL 25

Expert Comment

by:jogos
ID: 36558277
For the record, also the '* from openrowset....' is never correct because using it in the selectlist you need also the keyword SELECT and only 1 column specified.

select col1
,col2
,select dat1 from t2 where ....
from t1

And always evaluate off it is not a bad (slow) way off writing something that's supposed to be in a JOIN.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36558373
Please create inline/dynamic query.

Assign to variable and execute that variable.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 9

Accepted Solution

by:
mimran18 earned 2000 total points
ID: 36558441
Hi,
 here we go.
 
Drop table test
Go
Create table test
(ID int identity(1,1),
[File] varbinary(max),
)
Go

Drop table test2
Go
Create table test2
(ID int,
[FName] nvarchar(50),
)
Go
Insert  into test2 values (1,'1.jpg')
Insert  into test2 values (2,'2.jpg')

GO
Drop Procedure [test_BLOB]
Go
Create Procedure [test_BLOB]
as
Declare @Filename nvarchar(50)
Declare @Str as nvarchar(max)
Set @Str=''
Truncate table test 

DECLARE testCursor CURSOR for

SELECT [FName] FROM test2

OPEN testCursor

FETCH NEXT FROM testCursor
 INTO @Filename
 While @@FETCH_STATUS = 0
 Begin
 		 Set @Str ='INSERT INTO test([File])
		 SELECT  * FROM OPENROWSET( BULK ''D:\img\'+ @Filename + ''',SINGLE_BLOB)
		 AS test2'
         Print @Str
         Exec (@Str)
  FETCH NEXT FROM testCursor
   INTO @Filename
  End
   CLOSE testCursor     
   DEALLOCATE testCursor 
--End
GO
[test_BLOB]
Go
Select * from test

Open in new window

0
 
LVL 2

Author Closing Comment

by:karlomedallo23
ID: 36906808
Thank you!
0
 
LVL 25

Expert Comment

by:jogos
ID: 36908245
so same sollution accepted as I linked to in the first comment after pointing out problems with the given problem
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question