I was assigned a task few days back to generate comma separated file from SQL Server table, It is very easy to do so with BCP utility. Generally we used to use BCP from DOS prompt but herewith I am going to use BCP right from my SSMS with XP_CMDSHELL.
use Adventureworks
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO
--insert records
INSERT INTO emps
SELECT 'RITESH','MIS','ECHEM' UNION ALL
SELECT 'Rajan','MIS','mar'
declare @sql varchar(1000)
--creating emps.txt file in D drive from just created table above
select @sql = 'bcp Adventureworks..emps out d:\emps.txt -c -t, -T -S '+ @@servername
--print @sql
--generally, you should run BCP from command prompt, if you wish to do it from
--SSMS, you have to use xp_cmdshell
exec master..xp_cmdshell @sql
If your xp_cmdshell is disable, there is one small article to enable it at:
http://www.sqlhub.com/2009/05/enable-xpcmdshell-in-sql-server-2008.html
I have written some more interesting article on BCP which you can find at
http://www.sqlhub.com/2009/03/bcp-or-bulk-copy-program-in-sql-server.html
http://www.sqlhub.com/2009/03/bcp-export-data-from-sql-server-to-flat.html
http://www.sqlhub.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
2 comments:
You should blog things like this more.
People will judge you based on your quality.
Thanks for the comment, I always try my best to help the community more and more
Post a Comment