There
are a lots of ETL tools available to fetch the data from your database into the
file. But these ETL tools are not possible for each one and sometimes, we need
to create a file to store the data from our database in SQL and share these data files to business users/ data analysists/sales team for the data analysis to take the right decision.
In
this section, we will learn that how can we write the data into a file (Text or
CSV) with the help of SQL.
Note: 'Ole
Automation Procedures' component should be turned on as part of the
security configuration for your server and a system administrator can enable
the use of 'Ole Automation Procedures' by using sp_configure.
If
these components are turned off then you can use the following command in
your SQL Server Management Studio:
sp_configure 'Ole Automation Procedures',
1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options',
1
GO
RECONFIGURE;
After
the above command you will get the following message in SQL-
Configuration option 'Ole Automation Procedures' changed from 0 to
1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 1.
Run the RECONFIGURE statement to install.
|
We
will create a temp table to store the values as given below:
Use Tempdb
Go
----- create temp table
CREATE TABLE
EMPLOYEEMASTER
(EMPID INT, EMPNAME VARCHAR(20), SALARY INT)
---- insert data into temp table
INSERT INTO
EMPLOYEEMASTER VALUES
(101, 'RYAN ARJUN', 25000),
(102, 'ARJUN PAUL', 35000),
(103, 'RYAN GRAY', 15000),
(104, 'BILL SMITH', 25500),
(105, 'GARY KILL', 45000),
(106, 'SMITH WILL', 55000)
----- select data from the temp table
SELECT *
FROM EMPLOYEEMASTER;
|
Now
our data is ready to use. We need some local variables to hold the information
which are necessary to write the data into the file as given below:
--- declare local variable to hold the values
DECLARE @FS
int,
@OLEResult int,
@FileID int,
@filename varchar(100),
@oneline varchar(300),
@itemstring varchar(5000),
@uncpather varchar(200),
@uncfile varchar(200),
@datestr varchar(12)
|
We
need to set the file location in the filename variable. If file exists then we
need to delete that file from the disk. We need to open the file and write the
header first row as the header into the file as shown below:
--- set the file name into the temp folder
set @filename
= 'c:\temp\Employees.txt'
--- create the file object
EXECUTE @OLEResult
= sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--- check the file existence
--- if file exists than delete the file
EXECUTE @OLEResult
= sp_OAMethod @FS, 'FileExists', @FileID out, @filename
IF @OLEResult = 0
begin
PRINT 'FileExists'
--- delete the old file if exist on the location
EXECUTE @OLEResult
= sp_OAMethod @FS, 'DeleteFile', Null, @filename
---- print msg for confirmation
IF @OLEResult = 0
PRINT 'FileDeleted'
end
--Open a file to write the data into the file
execute @OLEResult
= sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult = 0
--- set the file header columns here
execute @OLEResult
= sp_OAMethod @FileID, 'WriteLine', Null, 'Id, Name, Salary'
--- check the file's open mode
IF @OLEResult = 0
PRINT 'OpenTextFile'
|
Now
we need a cursor to hold the values from the source and write the data into the
file row by row as shown below:
--- declare cursor for fetch value from the target
table
--- and write the cursor data into the targetted file
Declare @Emp_Cursor
Cursor
--- set value into the cursor with the separeted comma
between each column,
--- where comma is used to identify as a new column
into the file
Set @Emp_Cursor
= Cursor FOR
SELECT Convert(varchar,EMPID) +',"'+EMPNAME +'",'+ Convert(varchar,SALARY) FROM EMPLOYEEMASTER
ORDER BY EMPID
--- open cursor for the next step to insert data into
the target file
OPEN @Emp_Cursor
|
Now,
we need to write the data into the file row by row by the help of the cursor as
shown below:
--- open cursor for the next step to insert data into
the target file
OPEN @Emp_Cursor
Fetch next
from @Emp_Cursor
into @oneline
--- fetch records from the cursor as well as write the
data into the opened file
--- line by line
While (@@FETCH_STATUS =0)
Begin
---- if exists
if @@FETCH_STATUS
= 0
print @oneline
----- write the file
execute @OLEResult
= sp_OAMethod @FileID, 'WriteLine', Null, @oneline
---- Fetch the next line
Fetch next
from @Emp_Cursor
into @oneline
End
---- destroy the file Id
EXECUTE @OLEResult
= sp_OADestroy @FileID
EXECUTE @OLEResult
= sp_OADestroy @FS
--- close the cursor
Close @Emp_Cursor
--- destroy the cursor variable here
Deallocate @Emp_Cursor
|
After
follow the above steps, execute the query and our file is ready on the file
location.
At a Glance: this will be the final Query to create a
file in SQL as shown below:
Use Tempdb
Go
----- create temp table
CREATE TABLE
EMPLOYEEMASTER
(EMPID INT, EMPNAME VARCHAR(20), SALARY INT)
---- insert data into temp table
INSERT INTO
EMPLOYEEMASTER VALUES
(101, 'RYAN ARJUN', 25000),
(102, 'ARJUN PAUL', 35000),
(103, 'RYAN GRAY', 15000),
(104, 'BILL SMITH', 25500),
(105, 'GARY KILL', 45000),
(106, 'SMITH WILL', 55000)
----- select data from the temp table
SELECT *
FROM EMPLOYEEMASTER;
--- declare local variable to hold the values
DECLARE @FS
int,
@OLEResult int,
@FileID int,
@filename varchar(100),
@oneline varchar(300),
@itemstring varchar(5000),
@uncpather varchar(200),
@uncfile varchar(200),
@datestr varchar(12)
--- set the file name into the temp folder
set @filename
= 'c:\temp\Employees.txt'
--- create the file object
EXECUTE @OLEResult
= sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--- check the file existence
--- if file exists than delete the file
EXECUTE @OLEResult
= sp_OAMethod @FS, 'FileExists', @FileID out, @filename
IF @OLEResult = 0
begin
PRINT 'FileExists'
--- delete the old file if exist on the location
EXECUTE @OLEResult
= sp_OAMethod @FS, 'DeleteFile', Null, @filename
---- print msg for confirmation
IF @OLEResult = 0
PRINT 'FileDeleted'
end
--Open a file to write the data into the file
execute @OLEResult
= sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult = 0
--- set the file header columns here
execute @OLEResult
= sp_OAMethod @FileID, 'WriteLine', Null, 'Id, Name, Salary'
--- check the file's open mode
IF @OLEResult = 0
PRINT 'OpenTextFile'
--- declare cursor for fetch value from the target
table
--- and write the cursor data into the targetted file
Declare @Emp_Cursor
Cursor
--- set value into the cursor with the separeted comma
between each column,
--- where comma is used to identify as a new column
into the file
Set @Emp_Cursor
= Cursor FOR
SELECT Convert(varchar,EMPID) +',"'+EMPNAME +'",'+ Convert(varchar,SALARY) FROM EMPLOYEEMASTER
ORDER BY EMPID
--- open cursor for the next step to insert data into
the target file
OPEN @Emp_Cursor
Fetch next
from @Emp_Cursor
into @oneline
--- fetch records from the cursor as well as write the
data into the opened file
--- line by line
While (@@FETCH_STATUS =0)
Begin
---- if exists
if @@FETCH_STATUS
= 0
print @oneline
----- write the file
execute @OLEResult
= sp_OAMethod @FileID, 'WriteLine', Null, @oneline
---- Fetch the next line
Fetch next
from @Emp_Cursor
into @oneline
End
---- destroy the file Id
EXECUTE @OLEResult
= sp_OADestroy @FileID
EXECUTE @OLEResult
= sp_OADestroy @FS
--- close the cursor
Close @Emp_Cursor
--- destroy the cursor variable here
Deallocate @Emp_Cursor
----- drop the temp table
drop table
EMPLOYEEMASTER;
|
Output of the file as Text File
Output of the file as CSV File
Conclusion
By using these features of SQL, we can schedule these script to write the data into text or csv file and share these files to the business team.
By using these features of SQL, we can schedule these script to write the data into text or csv file and share these files to the business team.
Hope you enjoyed and found this essay useful. Please leave your questions and suggestions in the comments section. Many thanks for reading! 😊
Your support is greatly appreciated! If you found this article valuable, don’t forget to clap👏, follow✌️, and subscribe❤️💬🔔 to stay connected and receive more insightful content. Let’s grow and learn together!
⭐To learn more, please follow us —
http://www.sql-datatools.com
⭐To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
⭐To Learn more, please visit our Instagram account at —
https://www.instagram.com/asp.mukesh/
⭐To Learn more, please visit our twitter account at —
https://twitter.com/macxima
⭐To Learn more, please visit our Medium account at —
https://medium.com/@macxima
What if we want to add multiple worksheets into single excel file?.
ReplyDeleteKindly give the change required in above code.