bcp temp table within a SP

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » bcp temp table within a SP

bcp temp table within a SP

Rate Topic

Display Mode

Topic Options

Author

Message

John Chiu

Posted Monday, November 14, 2005 1:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 02, 2010 7:38 AM
Points: 193, Visits: 53

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.

Post #236983

Carl Federl

Posted Monday, November 14, 2005 2:28 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 2,090, Visits: 3,185

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

Post #236992

John Chiu

Posted Tuesday, November 15, 2005 6:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 02, 2010 7:38 AM
Points: 193, Visits: 53

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.

Post #237154

rudy komacsar - Doctor "X"

Posted Tuesday, November 15, 2005 10:07 AM

Hall of Fame

Group: General Forum Members
Last Login: Wednesday, August 25, 2010 11:26 AM
Points: 3,017, Visits: 1,910

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

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."

Post #237256

Michael Mockus

Posted Tuesday, August 22, 2006 9:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 10:10 AM
Points: 2, Visits: 32

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

CREATE PROCEDURE [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

Post #303224

erbabu

Posted Thursday, October 25, 2007 10:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 01, 2008 1:40 PM
Points: 2, Visits: 12

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

Post #415205

erbabu

Posted Saturday, October 27, 2007 7:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 01, 2008 1:40 PM
Points: 2, Visits: 12

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

Post #415749

clive van eeden

Posted Wednesday, January 09, 2008 12:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 05, 2008 6:42 AM
Points: 4, Visits: 29

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

end

hope it helps...

Clive

Post #440440

elenina_blue

Posted Friday, February 29, 2008 4:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 29, 2008 6:44 PM
Points: 1, Visits: 2

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

Post #462725

Jeff Moden

Posted Friday, February 29, 2008 6:00 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,426, Visits: 16,984

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".

"Data isn't the only thing that's supposed to have Integrity."

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #462745

« Prev Topic | Next Topic »

Permissions