T SQL-2005-BCP help

Question

Post reply

T SQL-2005-BCP help

James.N

SSC Enthusiast

Points: 193
More actions
May 4, 2009 at 9:42 pm

#129762

can anyone please help me with BCP program to export and import data to and from CSV file?
A BCP with stored procedure in which I will select all from my table with a condition and export all this data to a CSV file with out column headers.
some thing like
Select * from Table when Table.columa=null or Table.columnb=null
and need to export this to a location c:/docus/test1
And In second BCP procedure, I will need to truncate my stating tables first and then insert data from a CSV file to staging tables.
Can any one please help me with this.
I did something like this but not sure whether it is correct or not ?
create procedure SP1
AS
try
begin trans
select * from table where tab.cola = null or tab.colb =null
commit reans
begin catch
----
end catch
go
delcare @sql varchar(8000)
select @sql='bcp"exec sp1"queryout s:/bcp-----.txt -c-s,-T-S'+@@servername
exec......xp_cmdshell @sql(not sue how to execute cmdshell, is this right ?? )

Viewing 7 posts - 1 through 6 (of 6 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this statments

1) EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "D:\FE\XP_CMD.CSV" -T -c -t,'

2) declare @sql varchar(100),

@SQL1 varchar(1000)

select @sql = 'bcp "SELECT * FROM sysfiles" queryout "D:\FE\XP_CMD.CSV" -T -c -t,'

select @SQL1 = N'EXEC xp_cmdshell ' +''''+ @sql +''''

exec (@SQL1)

ARUN SAS

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

Did you already take a look at the examples provided by Nigel Rivett, at the following link? Particularly after the heading “Complete control – stored procedures”:-

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

BTW - I’m not sure why you are enclosing the Select within an Explicit Transaction?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

James.N SSC Enthusiast Points: 193 More actions · Answer 3

Hi , Thanks for your replies.

Please Ignore my example if it's wrong.

Can anyone please providers a example or sample for

1)How can I export data from DB tables to CSV file and

2)Import data from CSV files to DB tables.

Thanks in advance.

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello again,

If you want to use BCP then please see the examples provided at the link I mentioned before.

You could also consider letting Microsoft do the programming for you (if SSIS is Okay?) There is an option in the Import/Export Wizard to save the process as an SSIS Package. So all you need to do is run through a sample import and then a sample export and save the package on each occasion.

You can of course modify the saved packages later if needed.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

James.N SSC Enthusiast Points: 193 More actions · Answer 5

Thanks very much. I have exported data to CSv files using BCP sucessfully :).

Can anyone please let me know the process need to follow to import data from csv file to SQL server ?

Any example or sample ?

Thanks in advance.

John H Marsh SSChampion Points: 11671 More actions · Answer 6

Hello again,

Did you ever get the import working?

If not, there is a simple example here:-

http://www.cryer.co.uk/brian/sqlserver/howtoimportcsv.htm

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group