Category: SqlServer

Import large Excel file into SqlServer

I have to import the list of NGO from Romania to SqlServer.  Before seeing my solution , please do it yourself: the file could be found at and the name is Asociatii

I have tried with SSMS – no luck ( text will be truncated, even when I put nvarchar(max))

The I tried with flat file from CSV  – same

Finally, I have solved with a little bit of VBA : first I have tried to write the insert into file and execute with sqlcmd. The file was too large, so sqlcmd does not report any errors( and not insert anything)

So the solution was to generate a batch file that have this

sqlcmd  -S . -E -d InfoRo -Q “INSERT INTO [dbo].[Asociatii$]  ([Denumire]  ,[Numar inreg Reg National],[pozitie inchisa (da/nu)],[Starea actuala],[Judet],[Localitate],[Adresa],[Asociati/Fondatori],[Scop],[Consiliu director],[Apartenenta federatie],[HG utilitate publica],[Data HG utilitate publica],[F14])values( ////)”

generated by this VBA script

Option Explicit

Sub x()
Dim startInsert As String

startInsert = "sqlcmd  -S . -E -d InfoRo -Q ""INSERT INTO [dbo].[Asociatii$]"
         startInsert = startInsert & "  ([Denumire]"
         startInsert = startInsert & "  ,[Numar inreg Reg National]"
           startInsert = startInsert & ",[pozitie inchisa (da/nu)]"
           startInsert = startInsert & ",[Starea actuala]"
           startInsert = startInsert & ",[Judet]"
           startInsert = startInsert & ",[Localitate]"
           startInsert = startInsert & ",[Adresa]"
           startInsert = startInsert & ",[Asociati/Fondatori]"
           startInsert = startInsert & ",[Scop]"
           startInsert = startInsert & ",[Consiliu director]"
           startInsert = startInsert & ",[Apartenenta federatie]"
           startInsert = startInsert & ",[HG utilitate publica]"
           startInsert = startInsert & ",[Data HG utilitate publica]"
           startInsert = startInsert & ",[F14])values("
           Dim i As Long, j As Integer, text As String
           Dim insert As String
           For i = 2 To 86813
            insert = insert & startInsert
            For j = Asc("A") To Asc("M")
                text = Sheet1.Range(Chr(j) & i).text
                text = Replace(text, vbCr, " ")
                text = Replace(text, vbLf, " ")
                text = Trim(text)
                 text = Replace(text, "'", "''")
                text = Replace(text, "  ", " ")
                insert = insert & "'" & text & "',"
            Next j
            insert = insert & "null)" & """"
            insert = insert & vbCrLf
            If (i Mod 150 = 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
           Next i
           If (Len(insert) > 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
End Sub

Maintaining history of data


What is this about?

I tend to quote Fowler: “Usually when we see properties on a class, they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now, we also want to ask these questions about some point in the past when things may have changed. “

For example , we want to track every change that occurs at an Employee ( changing name, salary) and see the previous versions( what was the salary 2 years ago ?)

So what are the software solutions to do an audit log ? First, we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory

Solution 1 : Coding / Event Sourcing

That means code should maintain history himself – i.e. adding to the history table every modifications.

For Entity Framework this can be done easy, by overiding SaveChanges.

Demo at 

Solution 2 : Table Triggers

This can be done from database . There are triggers for insert , update, delete – and those triggers insert the data in the history table

Demo at

Solution 3, Temporal Tables, Microsot  Sql Server 2016 :

Documentation at 

Limitations at 

Demo at

Monitor SqlServer Stored Proc execution time

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see )
The code is for sql server 2005 and I have not realized without the help of
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcName] [varchar](128)
	[IdentifierAccess] [varchar](max)
	[DateAccess] [datetime] NULL DEFAULT (getdate())

2. Stored proc to insert data :

alter proc dbo.proc_Log(@procname varchar(100))

declare @userid varchar(100)
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user !
set @userid = SUSER_NAME()


3. In each stored procedure put this at beginning

DECLARE @ProcName nvarchar(128)


    exec proc_Log  @ProcName

And this at the end of stored proc :

    exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) 
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)

Sql Server Management Studio free awesome addons

The two addons that I can not live without in Sql Server and that, more , are free :

  1. SSMS Tools Pack – now ,
  2. SQL Search – now1.0


The SSMS Tools Pack maintains a history of your commands . More, it saves on the C:\SSMSTools\SQLQueryActionLog



The Sql Search finds a text in the objects in the database – very useful if you decide to change a column name and find all stored procedures that have this reference


Andrei Ignat weekly software news(mostly .NET)

* indicates required

Please select all the ways you would like to hear from me:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.