I am a regular reader of Pinal Dave's blog SqlAuthority. I always found something new in his blog to work with SQL Server. Here is something I would like to share: Get the recent executed SQL Queries from SQL Server.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)
AS destORDER BY deqs.last_execution_time DESC

If you enjoyed this post, make sure you subscribe to my RSS feed!

Using bulk operations using code with sql can be complex, time consuming and tedious for most of the programmers. I just used a stored procedure in one of my projects instead of traversing from cell to cell and save the value in the database........so I search the net and found this SQL Stored Procedure achieve this. I have just copied and paste the code as it is

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--drop procedure sp_excel_Convert_Excel_To_Table
--go
Create procedure [dbo].[sp_excel_Convert_Excel_To_Table]
(
@excel_full_file_name varchar(666)
,@convert_to_table_name varchar(50)
,@transfer_to_table bit=1
,@clear_existing_records_first bit=1
,@good int =null output
,@error_code int =null
,@error_description varchar(255) = null output
)
as
set nocount on
declare @command varchar(8000)
-- copy excel file under temp and change worksheet name
set @good=0
set @error_description = ''
set @error_code=0
declare @object int
,@hr int
,@src varchar(255)
exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '1. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=1
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '2. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=2
set @good=0
goto error
end
declare @workbook int
,@workbook_path_save_as varchar(666)
set @workbook_path_save_as = 'c:\test.xls'
exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@excel_full_file_name
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '3. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=3
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'Workbooks(1).Worksheets(1).Name','excel_data'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '4. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=4
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @workbook ,'SaveAs',null,@workbook_path_save_as
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '5. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=5
set @good=0
goto error
end
-- get full file name
exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).FullName',
@workbook_path_save_as output
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '6. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=6
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'--,@workbook out,@workbook_path
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
goto error
end
if @transfer_to_table = 1
begin
if @clear_existing_records_first=1
begin
set @command = 'delete from ' + @convert_to_table_name
exec (@command)
end
-- copy records from excel into table:
set @command = 'insert into ' + @convert_to_table_name +
' select * from ' +
' OpenRowSet(''MSDASQL'' '+
', ''Driver={Microsoft Excel Driver (*.xls)};'+
'DBQ=' + @workbook_path_save_as + ''''+
',''SELECT * FROM [excel_data$]'')'
print @command
exec (@command)
set @command = 'select * from ' + @convert_to_table_name
exec (@command)
end
error:
select @error_description as 'Error', @error_code as 'Error Code'
exec @hr = master.dbo.sp_OADestroy @workbook
if @hr <> 0
begin
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
end
exec @hr = master.dbo.sp_OADestroy @object
if @hr <> 0
begin
set @error_description = '8. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=8
set @good=0
end

If you enjoyed this post, make sure you subscribe to my RSS feed!

Performing BULK operations with SQL can be useful when you are trying to insert any type of file. Don't mess up with the complex code, just use a simple SQL query to insert any file type in SQL table. 

To perform this operation first you need to have a column with datatype as varbinary(max). See below example:

Create a table with an ID and Data as columns with INT and VARBINARY(MAX) as their Datatypes respectively.

Create table tblData
(
FileId int,
FileData varbinary(max)
)

Perform INSERT to Save a file. Here just change the location of the file you want to insert

Insert into tblData
(FileId, FileData)
SELECT 1, BulkColumn
FROM OPENROWSET(BULK N'D:\Software\siw.exe', SINGLE_BLOB) AS Document

Display table data after INSERT

Select * from tblData

If you enjoyed this post, make sure you subscribe to my RSS feed!

You have large data to import to SQL Server, and you have all the data in a CSV File (Comma Seperated Value file). Are you going to map the field one by one? 

OR just use a small function od SQL to import everything in the table. But before you proceed with this you need to map each column of CSV with the one with your table column. Look at the below quick example on how to use the BULK INSERT to import CSV file data into SQL Server.

Step 1: Create a CSV file.

Step 2: Create a table and map the fields accordingly: (Check below screenshot)

Here is the CODE:

BULK INSERT TestTable
FROM 'c:\TestData.csv'  -- Full path of the CSV file
WITH
(
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n'   --Use to shift the control to next row
)

If you enjoyed this post, make sure you subscribe to my RSS feed!