Data import - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Data import

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-05-22 : 23:26:02

I need to export the data from a table to generate excel file on different machine and after successful export to excel alert an email.this process needs to run every week.

Please help..

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-23 : 00:02:16

Here is one example. Note this excludes email.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Export_to_Excel] Script Date: 05/23/2012 13:29:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Export_to_Excel]

---Database_Table_Selection_for_Excel_Export_RP and Export_to_Excel need to be updated if additional tables added

@table_name nvarchar(max)

As

--Generate column names as a recordset
declare @columns nvarchar(max), @sql nvarchar(max), @data_file nvarchar(255), @file_name nvarchar(255), @db_name nvarchar(max)

--Set DB
SET @db_name = 'VC'

--Set Table Name
SET @table_name = (
CASE
WHEN @table_name = 'User Details' THEN 'UserDetails'
WHEN @table_name = 'Site Details' THEN 'SiteDetails'
WHEN @table_name = 'Site Authentication Details' THEN 'SiteAuthentication'
WHEN @table_name = 'Site Notification Details' THEN 'SiteNotificationDetails'
WHEN @table_name = 'Session Details' THEN 'SessionDetails'
WHEN @table_name = 'Event Details' THEN 'EventDetails'
WHEN @table_name = 'Event Comments' THEN 'EventComments'
WHEN @table_name = 'Operator Actions' THEN 'OperatorAction'
WHEN @table_name = 'Site Event Details' THEN 'SiteEventDetails'
WHEN @table_name = 'Resource Monitor' THEN 'SysMonitorRec'
WHEN @table_name = 'License Details' THEN 'LicenseDetails'
WHEN @table_name = 'Site Images' THEN 'SiteImages'
END )

--Set Storage Location
Set @file_name = (SELECT EntryValue + '\VCPRS\Exports\' FROM (SELECT EntryValue FROM VCSystemData WHERE EntryName = 'VCPDataRootPath') as t)
--Print @file_name

--Set Machine Name
Declare @MachineName nvarchar (255)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '\XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

-- Add TableName, Date, Time and .xls to file name
Set @file_name = @file_name + @table_name + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'
--Print @file_name

Select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
From information_schema.columns
Where table_name=@table_name

Select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
Select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
Set @sql='exec master..xp_cmdshell ''bcp "select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T -S "'+@MachineName+'"'''
--Print @sql
Exec(@sql)

--Generate data in the dummy file
Set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "'+@MachineName+'"'''
--Print @sql
Exec(@sql)

--Copy dummy file to passed EXCEL file
Set @sql= 'exec master..xp_cmdshell ''type "'+@data_file+'" >> "'+@file_name+'"'''
--Print @sql
Exec(@sql)

--Delete dummy file
Set @sql= 'exec master..xp_cmdshell ''del "'+@data_file+'"'''
--Print @sql
Exec(@sql)

RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources