To move all the table data from database to xls or

,

To move all the table data from database to xls or doc or txt files table
Many times we come accross to move data from database to external files such as word,xls or data files. Here is the script that move all tables data to specified files. Target file names are the table names.
specify the folder path, server name and @FILE_TYPE wether
.xls or .doc or .txt

---************************************************************************************************
-- AUTHOR : VEERESH V NASHI (DBA)
-- CREATED ON : 05-04-2006
-- PUROSE : To move all the table data from database to xls or doc or txt files table
---name as the file name

SET NOCOUNT ON
GO
declare @tables table( tid int identity,tname sysname)
declare @index int 
declare @Max int
declare @QUERY varchar(4000)
declare @QUERY1 varchar(4000)
DECLARE @Tname sysname
DECLARE @DBNAME SYSNAME
DECLARE @Folder_Path sysname
DECLARE @SERVER_NAME SYSNAME
DECLARE @FILE_TYPE VARCHAR(5)
insert into @tables (tname) 
select name  from sysobjects where xtype='u' 
set @Max = @@identity

------USER SETTINGS-----------------
SET @Folder_Path = 'D:\XLS'
SET @SERVER_NAME ='javauser3\veena'
SET @FILE_TYPE = 'XLS' -- EXCEL  OR DATAFILES LIKE TXT OR DOC 
------USER SETTINGS-----------------

SET @DBNAME = DB_NAME()
SET @QUERY =''
SET @QUERY1 =''
SET @Index = 1
WHILE @index <= @max
 BEGIN
SELECT @Tname = tname from @tables where tid = @Index 
	SET @QUERY1 ='SELECT  '
	SELECT @QUERY1 =  @QUERY1 + '''' +  QUOTENAME( NAME ,'''') + ''' AS ' +  NAME + ' ,'   FROM SYSCOLUMNS where id = object_id(@tname)  
	SET @QUERY1 = LEFT( @QUERY1,LEN( @QUERY1 ) - 1 ) 


 	SET @QUERY='SELECT '
 	SELECT @QUERY =  @QUERY + 'CAST ( '+NAME + ' AS VARCHAR('+ LTRIM( STR( LENGTH )) +')),'   FROM SYSCOLUMNS where id = object_id(@tname) 
 	SET @QUERY = LEFT( @QUERY,LEN( @QUERY ) -1 ) +' FROM '+@DBNAME+'.dbo.'+ @TNAME 

  
  	SET @QUERY1 = @QUERY1 +  ' UNION ALL ' + @QUERY 

	PRINT 'MOVING '+@tname +' TO ' + @Folder_Path +' AS FILE NAME ' +@TNAME +'.'+@FILE_TYPE 

        SELECT  @Query= 'master.dbo.xp_cmdshell 
  		''bcp.exe "'+@QUERY1 +  ' " queryout "'+@Folder_Path+'\' + @tname + '.'+@FILE_TYPE +'" -c -T -S '+@SERVER_NAME +''''

EXECUTE(@Query)
--PRINT (@Query1)
set @Index = @Index  + 1 
end

GO
SET NOCOUNT ON

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)