May 10, 2012 at 7:27 am
HI,
I would like to export table data to text file how can do this for each table in a database.
I dont have bussiness tool (SSIS) installed on it.
I need a BCP code..it should loop through each table in a database and export to destinaltion location.
- How to implement it using cursor.
May 10, 2012 at 10:20 am
I would opt for PowerShell + SMO + BCP.
Pseudocode:
1. Using SMO loop over table collection for your database.
2. Inside loop call BCP once for each table and output data to a file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 2:54 am
I returned following code...but it is not working fine
DECLARE @fileName VARCHAR(50)
DECLARE @name sysname
DECLARE @sqlcmd VARCHAR(1000)
SET @fileName = 'D:\data'
DECLARE table_Cursor CURSOR FOR
SELECT name FROM sys.objects where type='U';
OPEN table_Cursor;
FETCH NEXT FROM table_Cursor
into @name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_Cursor;
SET @sqlCmd = 'bcp "' + @name+ '" queryout ' + @fileName + ' -w -T'
EXEC xp_cmdshell @sqlCmd
END;
CLOSE table_Cursor;
DEALLOCATE table_Cursor;
GO
This codes need many changes.
i am not expert in coding.Can you please help me in this
--I need a script which has copy each table data (loop through each table in db) and keep it disk with table_name.txt (extension)
May 11, 2012 at 5:02 am
Simha24 (5/11/2012)
I returned following code...but it is not working fineDECLARE @fileName VARCHAR(50)
DECLARE @name sysname
DECLARE @sqlcmd VARCHAR(1000)
SET @fileName = 'D:\data'
DECLARE table_Cursor CURSOR FOR
SELECT name FROM sys.objects where type='U';
OPEN table_Cursor;
FETCH NEXT FROM table_Cursor
into @name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_Cursor;
SET @sqlCmd = 'bcp "' + @name+ '" queryout ' + @fileName + ' -w -T'
EXEC xp_cmdshell @sqlCmd
END;
CLOSE table_Cursor;
DEALLOCATE table_Cursor;
GO
This codes need many changes.
i am not expert in coding.Can you please help me in this
--I need a script which has copy each table data (loop through each table in db) and keep it disk with table_name.txt (extension)
I have made few changes in your query & its working now.
SET NOCOUNT ON
DECLARE @fileName VARCHAR(50)
DECLARE @name sysname
DECLARE @TableFQName NVARCHAR(500)
DECLARE @SchemaId INT
DECLARE @sqlcmd VARCHAR(1000)
DECLARE table_Cursor CURSOR FOR
SELECT name,[schema_id] FROM sys.tables
OPEN table_Cursor;
FETCH NEXT FROM table_Cursor into @name,@SchemaId;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = 'D:\data\'+@name+'.txt'
SET @TableFQName = QUOTENAME(DB_NAME())+'.'+QUOTENAME(SCHEMA_NAME(@SchemaId))+'.'+QUOTENAME(@name)
SET @sqlCmd = 'bcp ' + @TableFQName+ ' out "' + @fileName + '" -w -T'
--EXEC xp_cmdshell @sqlCmd
PRINT @sqlCmd
FETCH NEXT FROM table_Cursor into @name,@SchemaId;
END;
CLOSE table_Cursor;
DEALLOCATE table_Cursor;
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply