Bulk Insert into Temp table from Stored Procedure

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

SQL Server Development (2000)

Bulk Insert into Temp table from Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-29 : 08:46:59

Robert writes "Is it possible to do a Bulk Insert from within a stored procedure directly into a temporary table?

Here is the current syntax of a stored procedure that I have:
CREATE PROCEDURE dbo.TextFileBulkImport
@SourceFilePath varchar(200),
@FormatFilePath varchar(200),
@RowNumber int
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @str_command nvarchar(500)

SET @str_command = 'BULK INSERT [Customers] FROM ''' + @SourceFilePath + ''' WITH (formatfile = ''' + @FormatFilePath + ''', firstrow =' + cast(@RowNumber as nvarchar) + ')'

EXEC SP_EXECUTESQL @str_command"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 09:17:13

If you want to use Dynamic SQL then make use of Global temp table

Instead of Customers use ##Customers
Create table ##Customers with same structure of Customers and use that in query

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-29 : 09:30:48

If you use a global temp table, you should generate the table name with a NEW_ID() each time to make sure it is unique. This will prevent it from accidentally using the same name that another process is using.

declare @temp_table_name varchar(60)

select @temp_table_name = '##temp_'+replace(newID(),'-','')

select name = @temp_table_name

name                                                         
------------------------------------------------------------ 
##temp_9FCC9D433C094825A1FD50349FFFD7C9

(1 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 09:36:14

Thats good idea MVJ

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-29 : 10:22:47

quote:
Originally posted by madhivanan

Thats good idea MVJ

Madhivanan

Failing to plan is Planning to fail

Let's just call it hard experience.

If two applications are using a global temp table with the same name, it can cause problems that are extremely hard to debug.

I had one recently where a report failed every day, but ran just fine if you reran it. Turned out that the developer used the same global temp table name for both stored procedures, and when one of the procs dropped the table, the other would fail.

I made them go back an remove all the global temp tables, and I don't let them use them anymore, unless there is no other way. If is is needed, I make them do it the way I posted.

CODO ERGO SUM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-29 : 11:13:17

With bulk insetr you can insert into a temp table (from dynamic sql if you wish) there is not need for a global temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 00:41:55

quote:
Originally posted by nr

With bulk insetr you can insert into a temp table (from dynamic sql if you wish) there is not need for a global temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

So querying on that temp table should be in the same block?

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-30 : 03:41:28

Once the temp table is created it can be populated and queried in any nested batch. Any data updates to the temp table in any nested batch will be available to all batches untill the temp table is dropped.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources