Fun with inner join

You know any alternative of INNER JOIN??

Here is it....

Table Structure:

Employee Table
LastName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
Robinson	34
Smith	34
Jasper	NULL

Department Table
DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

ORIGINAL ONE:

SELECT Employee.lastName, Employee.DepartmentID, Department.DepartmentName

FROM Employee INNER JOIN Department

ON Employee.DepartmentID = Department.DepartmentID;

ALTERNATIVE ONE:

SELECT Employee.lastName, DepartmentID, Department.DepartmentName

FROM Employee INNER JOIN Department

USING(DepartmentID);

Happy Coding :)

Insert data to table from another table

Here is the example, how you can transfer data from one table to another;
(asumming, tables have been created and structures are same)


SET IDENTITY_INSERT [DB1].[dbo].TableName ON

insert into [DB1].[dbo].TableName select * from [DB2].[dbo].TableName

SET IDENTITY_INSERT [DB1].[dbo].TableName OFF

Above 3 lines:
1. Disable Identity.
2. Dump data from table.
3. Enable Identity again.


Hope you like it.

Select Query ... group by Date

Most of the time we use, GROUP BY, in our sql queries, sometimes it may require to write a query Group By Date.

In this case, if the date field is DateTime in your table column, it won’t give correct data because it contains Date + Time, the time part makes difference, so we have to ignore the time part when writing query for group by with Date column.

What what what…????

Relax and see the example below you will understand clearly…

I have a table:

CREATE TABLE [dbo].[Table_1](

[name] [nchar](10) NULL,

[date] [datetime] NULL,

[add] [nchar](10) NULL

)

Note: date may be 2009-02-02 00:00:00.000 or 2009-02-02 08:20:10.000

Now compare both results below:

=======================================================

select date,[name],[add] from Table_1 group by date,[name],[add]

date name add

----------------------- ---------- ----------

2009-02-02 00:00:00.000 a 111

2009-02-02 00:00:40.000 ccc 333

2009-02-02 00:01:00.000 bb 222

2009-02-02 00:07:00.000 aa 66

2009-02-02 01:00:00.000 aaa 77

2009-02-03 00:00:40.000 ddd 444

2009-02-03 00:00:40.000 eee 555

(7 row(s) affected)

=======================================================

select

DATEADD(dd, 0, DATEDIFF(dd, 0, date)) date,

[name],

[add]

from

Table_1

group by

DATEADD(dd, 0, DATEDIFF(dd, 0, date)),

[name],

[add]

date name add

----------------------- ---------- ----------

2009-02-02 00:00:00.000 a 111

2009-02-02 00:00:00.000 aa 66

2009-02-02 00:00:00.000 aaa 77

2009-02-02 00:00:00.000 bb 222

2009-02-02 00:00:00.000 ccc 333

2009-02-03 00:00:00.000 ddd 444

2009-02-03 00:00:00.000 eee 555

(7 row(s) affected)

=======================================================

Look at First column of both restult,in second result all time are set to 00:00:00:000

Observ the first and second column, First result, even though dates are same, they appears to be different, and solution/fix is in second result…

Hope it helps you some where… some time…

Happy coding !!! J

SQL Server - IDENTITY - How to change/reset the IDENTITY values?

SQL Server - IDENTITY - How to change/reset the IDENTITY values?

In the previous post, we saw that the IDENTITY values can go out of sequence in several cases. Some times, you might decide to delete all rows from a table, and start filling fresh data. At this time, you might prefer to reset the IDENTITY value to start a new sequence.

Run the following script to create a sample table.

IF OBJECT_ID('Employees','U') IS NOT NULL
   DROP TABLE Employees
 
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

Let us now delete all rows from this table and insert a new row.

DELETE FROM Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
3           Jacob
*/

As we figured earlier, the IDENTITY value started with 3, not with 1. Let us now see how we can reset the IDENTITY value back to 1.

TRUNCATE TABLE resets IDENTITY values

When you truncate a table, the IDENTITY value will be RESET. Here is an example:

TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
*/

Note that the new IDENTITY value started from 1.

DBCC CHECKIDENT

You can use DBCC CHECKIDENT to reset the IDENTITY seed. Before we run the example, let us rebuild the table again.

IF OBJECT_ID('Employees','U') IS NOT NULL
   DROP TABLE Employees
 
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

Now, let us try DBCC CHECKIDENT

DELETE FROM Employees
DBCC CHECKIDENT('Employees',RESEED, 0)
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
*/

It is also possible to change the NEXT identity value to a value of your choice. The following example changes the current identity value to be 99, so that the next value generated will be 100.

DBCC CHECKIDENT('Employees',RESEED, 99)
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
100         Bob
*/

Source:

http://beyondrelational.com/blogs/jacob/archive/2009/01/30/sql-server-identity-how-to-change-reset-the-identity-values.aspx