Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I make multiple insert for openrowset bulk?

Posted on 2011-09-18
6
Medium Priority
?
665 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

636 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