I am working on BUlk Insert from a file to a table in SQL server 2005.
View Complete Post
I got a scenario where we want to move some 30k records from a staging table to main table while moving we need to compare the columns from staging to history table and insert some new columns along with the columns in staging tables.
I will give one example.
Lets take staging table with scol1, scol2, scol3
Main table have mcol1, mcol2, mcol3, mcol4,mcol5,mcol6
History table have hcol1,hcol2, hcol3,hcol4, hcol5,hcol6
so i wil move the staging table to Main table by comparing columns scol1, scol2, scol3 with hcol1,hcol2, hcol3 and mcol4,mcol5,mcol6 will contain the 1 if values differ or 0 if it contains same value
We are using SQL Server 2005 x86 enterprise edition. We have one table which has 13 crores / 130 million records. When we insert a record in that table it is taking around 30 seconds. Can u please help optimize it.
The table currently has 1 primary key (Clustered Index) and 4 non clustered index.
When loading 50MB file with
BULK INSERT #FlatStage
WITH (ROWTERMINATOR = '\n')
I get the beginning of the file shifted to the middle approximately. Otherwise the lines are in order (so the document starts in the middle, progresses to bottom, continues at the top).
To me this should be much easier, but I can't seem to retain the values that are put in the Insert template upon a validation failure or when the sql fails. This will prevent the user from having to retype everything agin in the event of a failure.
As you can see I am using the Sub Insert_Click and not using an insert through the wizard. so the fields are unbound textboxes on the form. There are 2 dropdowns that provide choices for the other fields.
Any help would be appreciated. I pasted in the code using the text only option becuase the format was getting messed up. I porboably need more background on why the fields are blanking out.
See Code below
Protected Sub InsertButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Dim sqlparm As String
Dim con As Data.SqlClient.SqlConnection = Nothing
Dim cmd As Data.SqlClient.SqlCommand = Nothing
Dim dr As Data.SqlClient.SqlDataReader = Nothing
Dim sql As String = ""
Dim x As Integer
' Dim x As Integer
If CType(Me.fvwAppSecurityDetails.FindControl("CorpIDTextBox"), TextBox).Text = "" Then
Me.lblMessage.Text = "CorpID cannot be blank"
Else : Page.Session("CorpID&
I have issues with Concurrency.
there has been times when updates happen on my fact table while some users read data from it at the same time, it takes a very long time and there is some blocking issues.
1.My inserts happen as Bulk inserts. I would like to know if I change my query to use NOLOCK will it still be blocked by the Bulk insert when i read? so does Bulk insert do an exclusive lock, so i cant read data?
2. changing my query to use NOLOCK is it benefical?
Please help me