Import Excel Data Into SQL Server (Skip Rows)

I have included two snippets:
The first one is to use SqlBulkCopy:

protected void Button1_Click(object sender, EventArgs e) 
    {             // Connection String to Excel Workbook 
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\property.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; 
 
        // Create Connection to Excel Workbook 
        using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString)) 
        { 
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(" Select CountyCode,PropertyAppraiserName FROM [county$]", connection); 
 
            connection.Open(); 
 
            // Create DbDataReader to Data Worksheet 
            using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader()) 
            { 
                // SQL Server Connection String 
                string sqlConnectionString = @"Data Source=myServer;Initial Catalog=mytest1;Integrated Security=True"; 
 
                // Bulk Copy to SQL Server 
                using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString)) 
                { 
                    
                    bulkCopy.DestinationTableName = "dbo.PropertyAppraiser"; 
 
 
                    //Define ColumnMappings: source –--destination  
 
                    bulkCopy.ColumnMappings.Add("CountyCode", "DesCountyCode"); 
                    bulkCopy.ColumnMappings.Add("PropertyAppraiserName", "DesPropertyAppraiserName"); 
                   
 
 
                    DataTable mydt = new DataTable(); 
 
                    mydt.Load(dr); 
 
                    IEnumerable<DataRow> RowsSkip5 = mydt.AsEnumerable().Skip(5); 
 
 
 
                    bulkCopy.WriteToServer(RowsSkip5.ToArray()); 
 
                    //bulkCopy.WriteToServer(dr); 
 
 
                } 
            } 
        } 
    }

The second one is to use OPENROWSET:


  protected void Button2_Click(object sender, EventArgs e) 
    { 
 
        // SQL Server Connection String  
        string sqlConnectionString = @"Data Source=myInstance;Initial Catalog=myDBName;Integrated Security=True"; 
        SqlConnection conn = new SqlConnection(sqlConnectionString); 
        string strSQL = @"INSERT INTO dbo.PropertyAppraiser (DesCountyCode, DesPropertyAppraiserName) SELECT F1, F2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\property.xls;HDR=no', 'SELECT * FROM [county$a5:g]')"; 
        SqlCommand cmd = new SqlCommand(strSQL, conn); 
 
        conn.Open(); 
        cmd.ExecuteNonQuery(); 
        conn.Close(); 
 
    }

Advertisements

New Analytic Functions (Transact-SQL) in SQL Server 2012

SQL Server 2012 introduces some analytic functions to compute moving averages,
running totals, percentages or top-N results within a group.
Here is the list of them:

CUME_DIST
LEAD
LAG 
FIRST_VALUE
LAST_VALUE 
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK  

The link:
http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx


Updated OVER Clause (Transact-SQL) SQL Server 2012

Definition from Books Online:

The OVER clause defines a window or user-specified set of rows within a query result set.
A window function then computes a value for each row in the window.
You can use the OVER clause with functions to compute aggregated values such as moving averages,
cumulative aggregates, running totals, or a top N per group results.

The extended functions of OVER can simplify the calculation of aggregated value in query.

 
Syntax:

OVER (
       [ <PARTITION BY clause> ]
       [ <ORDER BY clause> ]
       [ <ROW or RANGE clause> ]
      )

<PARTITION BY clause> ::=
PARTITION BY value_expression , … [ n ]

<ORDER BY clause> ::=
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,…n ]

<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>

<window frame extent> ::=
{   <window frame preceding>
  | <window frame between>
}

…..
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.110).aspx

One example:

SELECT BusinessEntityID, TerritoryID
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
    ,DATEPART(yy,ModifiedDate) AS SalesYear
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                             ORDER BY DATEPART(yy,ModifiedDate)
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;


IDENTITY (Function) (Transact-SQL)

The function IDENTITY Is used only in a SELECT statement with
an INTO table clause to insert an identity column into a new table.

IDENTITY (data_type [ , seed , increment ] ) AS column_name

Some samples:
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable
SELECT  IDENTITY(smallint, 100, 1) AS ContactNum,
        FirstName AS First,
        LastName AS Last
INTO Person.NewContact
FROM Person.Contact;


Updated ORDER BY clause Syntax in SQL Server 2012

Syntax:
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,…n ]
[ <offset_fetch> ]
<offset_fetch> ::=
{
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

 

Some examples with OFFSET and FETCH NEXT:

USE AdventureWorks2012;
GO
— Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

— Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

— Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

 

 

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx


Sequence Numbers in SQL Server 2012

A sequence is a user-defined schema bound object that generates
a sequence of numeric values according to the specification with
which the sequence was created.

 

The syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

A sample with all arguments:

CREATE SEQUENCE Test.DecSeq
    AS decimal(3,0)
    START WITH 125
    INCREMENT BY 25
    MINVALUE 100
    MAXVALUE 200
    CYCLE
    CACHE 3
;

 

http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx


New Statement THROW (Transact-SQL) SQL Server 2012

Throw introduces a new way to raise an exception in CATCH block of a TRY…CATCH construct in SQL Server 2012.

The syntax is:
THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

If you use THROW outside the Try … Catch block,
you must use parameters (You don’t have to if you use it within the Catch block.

The differences between the RAISERROR and THROW statements are:

1.The error_number parameter does not have to be defined in sys.messages.
2.The message parameter does not accept printf style formatting.
3.There is no severity parameter. The exception severity is always set to 16.

More Information and examples:
http://msdn.microsoft.com/en-us/library/ee677615(v=sql.110).aspx