Find LastIndex in SQL Server

CREATE FUNCTION dbo.LASTINDEX(@STRING VARCHAR(8000), @CHAR CHAR)
RETURNS INT
AS
BEGIN
RETURN LEN(@STRING) – CHARINDEX(@CHAR, REVERSE(@STRING), 1 + 1)
END

export excel data into sql server using c#

Condition :
excel file columns and sql server tables columns should be same and datatype also should be same.

public void Exel2Sql()
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string ConnectionString = @”server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;”;
string connstr = @”Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls”;
using (connection = new OdbcConnection(connstr))
{
OdbcCommand command = new OdbcCommand(“Select * FROM [Sheet1$]”, connection);

//you can change [Sheet1$] with your sheet name

connection.Open();

// Create DbDataReader to Data Worksheet

using (OdbcDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server

using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = “Names”;
//”Names” is the sql table where you want to copy all data.

bulkCopy.WriteToServer(dr);
}
dr.Close();
}

}

bulkCopy.Close();
connection.Close();
}

Genereate Serial Number in Sql Server 2000 or 2005 query

In Sql Server 2005 and above version you can generate serial number as:

select row_number() over(order by empid) as serial_Number, empid,empname from emp

In Sql server 2000, you can generate serial number as:

select (select sum(1) from emp a where a.empid<=obj.empid) as serial_number,* from emp obj

Insert Excel data to Sql and Sql table data to Excel file

First execute this procedure

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

GO

RECONFIGURE;

GO

After executing procedure then write the query

to insert sql table data to excel file.

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xls;HDR=YES;’,
‘SELECT * FROM [SheetText1$]’)
SELECT * from TableName
_____________________________________________________________________________________

to insert excel data to sql table

Insert into dbo.tableName
select * from openrowset(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;database=C:\contact.xls’,
‘select * from [Sheet1$]’)

NOTE:
Excel columns and Table Columns should be same.

Reset or Reseed Identity on tables with identity column in sql server

Run this command to reset or reseed Identity columns of a table.

exec sp_MSforeachtable
‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1
BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’

Once you run this query on a table it will reset Identity column always.