Solved

How do I make multiple insert for openrowset bulk?

Posted on 2011-09-18
6
650 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Accepted Solution

by:
mimran18 earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.​
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now