Dynamic Query For Bulk Insert - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Query For Bulk Insert

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 16:29:00

I need to insert some data from text from a specific location. My stored procedure for this looks like this. This is based on an article I found in this site


CREATE PROCEDURE [dbo].[usp_ExtractTextFiles]
@filepath varchar(500),
@viewname varchar(100)
AS

Declare @sql varchar (1500)

select @sql = 'Bulk Insert testdb.dbo.'
select @sql = @sql+@viewname
select @sql = @sql+ 'from '
select @sql = @sql+@filepath
Exec (@sql)
go

When I tried to execute this SP with the following parameters
'\\server\all\summmary062005.txt' and 'Temp_View_Summary062005'
I'm getting an error stating
Line 1: Incorrect syntax near '\'.
An error occurred during execution of the INSERT statement.

What could be the problem here?

Thanks

Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-10 : 16:46:45

Print the @SQL command for us.

--Exec(@sql)
PRINT @SQL

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 16:56:56

quote:
Originally posted by tduggan

Print the @SQL command for us.

--Exec(@sql)
PRINT @SQL

Tara

Tara,

Print displayed this.

Bulk Insert testdb.dbo.Temp_View_Summary from \\server\all\summary062005.txt

I believe the single qoute is missing for the @filepath, If I'm not wrong the statement should had printed something like this...
Bulk Insert testdb.dbo.Temp_View_Summary from '\\server\all\summary062005.txt'

Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-10 : 17:02:45

Yes that is correct.

select @sql = 'Bulk Insert testdb.dbo.'
select @sql = @sql+@viewname
select @sql = @sql+ 'from '
select @sql = @sql+''''+@filepath+''''

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 17:14:24

Its working fine now...Thanks Tara.
Now the sp looks like this


ALTER PROCEDURE [dbo].[usp_ExtractTextFiles]
@filepath varchar(500),
@viewname varchar(100)
AS

Declare @sql varchar (1500)

BEGIN TRAN

select @sql = 'Bulk Insert testdb.dbo.'
select @sql = @sql+@viewname
select @sql = @sql+ ' from '''
select @sql = @sql+@filepath+''''
Exec (@sql)
IF ( @@ERROR <> 0 )
	BEGIN
	
	PRINT 'An error occurred during execution of the INSERT  statement.'
		ROLLBACK TRAN
--		SELECT 0
	END
ELSE
BEGIN
COMMIT TRAN
END
GO

When I execute this, the source text file has 42 rows this 42 rows goes into database but the message I get is bit wierd


Note: Bulk Insert through a view may result in base table default values being ignored for NULL columns in the data file.

(42 row(s) affected)


(42 row(s) affected)

Stored Procedure: testdb.dbo.usp_ExtractTextFiles
	Return Code = 0

Since I'm inserting data to the base table using view,I understand the "Note" part.But 42 row(s) affected doesnt make any sense for me...Even though that message is displayed twice only 42 rows are actually inserted...Looks like something wrong with the TRAN statements.

Any Ideas?

Thanks

Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-10 : 17:36:29

I've never seen a transaction around a BULK INSERT command. I'm not sure that it would work, especially since this is being done dynamically.

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 17:47:15

Usually I used to have transaction in the SP. I'll remove the transaction statements and see how it works...

Thanks

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 18:00:25

When i try to execute this
Bulk Insert testdb.dbo.Temp_View_Summary from '\\192.168.1.190\all\summary062005.txt'

I get this error
Could not bulk insert because file '\\192.168.1.190\all\summary062005.txt' could not be opened. Operating system error code 5(Access is denied.).

If I give machine name it works...Any ideas?

Thanks

Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-10 : 18:07:44

The person running the code does not have permissions to the "all" share.

Having permissions to a directory is different than having permissions to a share, even if they resolve to the same location.

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 18:19:48

quote:
Originally posted by tduggan

The person running the code does not have permissions to the "all" share.

I believe this is in context with the Sql User trying to execute the query...

quote:

Having permissions to a directory is different than having permissions to a share, even if they resolve to the same location.

resolving for machine name and not resolving for IP address for the same user...Interesting... Anyway I figured a work around in asp.net

Thanks

Karunakaran

peter@KLM
Starting Member

6 Posts

Posted - 2006-02-17 : 09:39:13

I have seen this trick to make a procedure with a parameter in it, but is it possible to have this part of T-SQL in a loop with a Cursor.
Because through the Cursor I'd like to handle x number of files sequentually. And each file name has a different name. The filenames and network-location are already in a SQL-table as fields: FileName and FileId.
So my procedure would look like

-- variables: @Folder_basis and @Folder_in
-- are set earlier in the procedure.

DECLARE Kopieer_cursor CURSOR FOR
SELECT rtrim(upper(FileName)) as FileName
, rtrim(upper(FileId)) as FileId
FROM ##FileNamesAndOrgId

OPEN Kopieer_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM Kopieer_cursor
INTO @FileName, @FileId

WHILE @@FETCH_STATUS = 0

BEGIN

create procedure [dbo].[usp_TEST] <<<<<<----- cursor with error mess
AS

declare @filepath varchar(500),
@viewname varchar(100)

set @filepath =@Folder_basis + @Folder_in + @FileName
set @viewname = @TempTable2

Declare @sql varchar (1500)

BEGIN TRAN

select @sql = 'Bulk Insert '
select @sql = @sql+@viewname
select @sql = @sql+ ' from '''
select @sql = @sql+@filepath+''''
Exec (@sql)
IF ( @@ERROR <> 0 )
BEGIN

PRINT 'An error occurred during execution of the INSERT statement.'
ROLLBACK TRAN
-- SELECT 0
END
ELSE
BEGIN
COMMIT TRAN
END
GO

drop procedure [dbo].[usp_TEST]

FETCH NEXT FROM Kopieer_cursor
INTO @FileName, @FileId

END

But my proc runs into error at the pointer. What is wrong?????

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-17 : 11:10:33

Rather than cursors, see if this will help you.
http://www.nigelrivett.net/SQLTsql/BCPAllTables.html
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Thanks

Karunakaran

peter@KLM
Starting Member

6 Posts

Posted - 2006-02-20 : 06:04:15

Karunakaran

Thank you for your reply;
Specially the second link was very interesting.
It needed a small adjustment. Because My pathname had a space in its name, I had to put double quotes (") around the FilePath variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources