How to Solve problems in SQL with Identity






I was working with identity and i had found an problem and a solution to solve these kind of problems.


To see which number is on the identity column:

--
-- See what is the Identity in the table Articles
--
DBCC CHECKIDENT

(

articles


)
Response is:
Checking identity information: current identity value '6', current column value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To setup with a new number on the identity column

DBCC CHECKIDENT

(

articles,reseed ,6000


)

it returns

Checking identity information: current identity value '1000', current column value '6000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note:
Don´t use truncate to delete table records it´s faster but it resets the identity column use instead the delete statement

Select Excel


I am going to show on select i use to read excel files XLS




Example:
Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\import\Excel_file.xls;Extended Properties=Excel 8.0')...Sheet1$

To do a proper use of these you must have your excel with headings and these heading must be unique and where you see Sheet1$ is the name of your sheet in your excel remember to keep these name simple and without spaces. The columns must be well formated or you don´t get the results you expect.


Help the world go to http://www.ami.pt