Solved

How do I make multiple insert for openrowset bulk?

Posted on 2011-09-18
6
657 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion schedule 13 35
SQL Calculation syntax based on a parameter 2 32
Problem with MySQL query - graph 3 28
Need to replicate a Log table 4 14
In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

696 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