BCP - 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

General SQL Server Forums

Script Library

BCP

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-20 : 10:12:59

I'm trying to come to terms with BCP.

Exporting the data to the file is not a problem. Getting it back out again is proving a little harder.
First I thought I'd access the FileSystemObject to copy the file from the source server to the target server - but I couldn't get the syntax right. I tried searching here and google but no luck.

Now I'm trying to bulk insert it directly from the file on the other server from inside a DTS package, like this:
DECLARE @MyPath VARCHAR (200)
SET @MyPath = @MyGlobalDTS_variable

BULK INSERT MyDB.dbo.MyTable
FROM @MyPath + 'myfile.txt'
WITH
(
DATAFILETYPE = 'native'
)

But it doesn't like the syntax of trying to build the path up dynamically.

Any ideas?

Also, even if I got it working, would it be slower than copying the file first? (The file is 400 meg)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-20 : 10:39:01

You need to use Dynamic SQL
Try this


DECLARE @MyPath VARCHAR (200)
SET @MyPath = @MyGlobalDTS_variable
declare @SQL varchar(300)
Set @sql='
BULK INSERT MyDB.dbo.MyTable
FROM '''+@MyPath + 'myfile.txt''
WITH 
(
DATAFILETYPE = ''native''
)'
Exec(@sql)

Madhivanan

Failing to plan is Planning to fail