bcp temp table within a SP

Question

bcp temp table within a SP

John Chiu

SSCrazy

Points: 2619
More actions
November 14, 2005 at 1:57 pm

#364166

I need bcp out/in chunck of data within a SP. The logic is like
1. create local temp table
2. select data into the above local temp table
3. bcp out the data from the local temp table

Is it possible to accomplish within a SP. It seems when bcp, it establish a new session that can't access that local temp table.

Any input will be sppreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Local temporary tables are visible only to the creating session but global temporary tables are visible to all sessions.

To create a global temporary table, prefix the table name with two number signs such as

Create table ##table_name

You need to be carefull with the life span of Global temporary tables, as they are automatically dropped when the session that created the table ends AND every other connection stops its reference.

This might be easier just to create a permanent table in tempdb and then drop it at the end of the process.

SQL = Scarcely Qualifies as a Language

John Chiu SSCrazy Points: 2619 More actions · Answer 2

Global temp table won't work for me in my situation. As this SP need create/run parallel in multiple databases in a huge SQL server (SAN) for various clients.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Why even use a 'temp' table when you can bcp out the data directly through a view. This trick should work in your situation.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Michael Mockus SSC Enthusiast Points: 136 More actions · Answer 4

This is an issue I have also run across in SQL 2005. It works fine using BCP from SQL 2000 but BCP from 2005 seems to handle the temporary table scope differently. Just as an example BCPing out the following:

Create a stored procedure

CREATEPROCEDURE [dbo].[gsp_foo]

AS

BEGIN

CREATE TABLE #dog(Col int)

INSERT INTO #dog VALUES(1)

SELECT * FROM #dog

DROP TABLE #dog

END

BCP Queryout from the procedure in 2005

c:\>BCP "EXEC dbc.gsp_foo" queryout "text.txt" -SRemoteServer -T

Results

SQLState = 42S02, NativeError = 208

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#dog'.

This code works just fine if you run the BCP from a SQL 2000 server.

Puzzled,

Michael Mockus

erbabu SSC Enthusiast Points: 110 More actions · Answer 5

So, what is the solution to this problem. We are also facing this.

erbabu SSC Enthusiast Points: 110 More actions · Answer 6

The following approach worked for us. Any drawback in this? Added 'fmtonly off' for BCP.

create procedure test1

as

set nocount on

IF(1 = 0)

BEGIN

select name = '' , id = ''

set fmtonly off

return

end

select top 10 name , id into #temp1 from sysobjects

select * from #temp1

clive van eeden SSC Enthusiast Points: 186 More actions · Answer 7

Hi, maybe this can help you. i did a similiar thing to what you are trying, just adapt your temp table and select query for the BCP...

CREATE PROCEDURE [dbo].[PROC_NAME]

AS

BEGIN

declare @CLOCKINGS varchar(100)

declare @LogID varchar(100)

declare @clock varchar (255)

DECLARE @bcpCommand varchar(2000)

declare @error varchar (100)

-- Create temp table to store data

create table Temp_Table (CLOCK varchar(255),

[LogID] [nvarchar](32) NULL,)

-- Create a new cursor

declare temp_cursor cursor for

select ssMainData.dbo.CLOCKINGS.[FILE], ssMainData.dbo.CLOCKINGS.LogID

from ssMainData.dbo.CLOCKINGS

-- Open the cursor and retrieve the first record

open temp_cursor

fetch next from temp_cursor

into @CLOCKINGS, @LogID

--- Loop through the recordset and add data to temp table

while @@fetch_status = 0

begin

insert into Temp_Table

values (@CLOCKINGS, @LogID)

---Fetch next record from the recordset

fetch next from temp_cursor

into @CLOCKINGS, @LogID

end

-- Close the cursor and release resources

close temp_cursor

deallocate temp_cursor

begin

--- Create import file using BCP through xp_cmdshell

SET @bcpCommand = 'bcp "select ssMainData..TEMP_TABLE.CLOCK FROM ssMainData..TEMP_TABLE" queryout c:\dump.txt -T -S CVE\CVE -c -t'

EXEC ssMainData..xp_cmdshell @bcpCommand

END

--- Drop temp_table

begin

drop table ssmaindata..temp_table

end

hope it helps...

Clive

elenina_blue Valued Member Points: 55 More actions · Answer 8

Hi

I need to know that if bcp import only text archive (*.txt)

I neen to know if bcp imports *.DBF (Foxpro)

Thank you very much

Elena Susy Moya Huamanchumo

email: elenamoya@pexport.com.pe

elenina_blue@hotmail.com

moya.es@pucp.edu.pe

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

BCP will not import .DBF files. You'll need to use DTS for that. Or, you can use a linked server or OpenRowSet with the .DBF provider to link directly to the files. I've not done that to .DBF files before so I can't walk you through it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)