Saturday 27 July 2013

Normalization in sql server

  Database normalization is the process of efficiently organizing data in a database. There are two reasons
of the normalization process:

1) Eliminating redundant data,for example, storing the same data in more than one tables.
2) Ensuring data dependencies make sense.

Both of these are worthy goals as they reduce the amount of space a database consumes and
ensure that data is logically stored. Normalization consists of a series of guidelines that
help guide you in creating a good database structure. Normalization guidelines are divided
into normal forms; think of form as the format or the way a database structure is laid out.
The aim of normal forms is to organize the database structure so that it complies with the
rules of first normal form, then second normal form, and finally third normal form.
It's your choice to take it further and go to fourth normal form, fifth normal form, and so on,
but generally speaking, third normal form is enough.

A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)

A) First Normal Form ::

First normal form (1NF) sets the very basic rules for an organized database:
1) Define the data items required, because they become the columns in a table.Place related data
items in a table.
2) Ensure that there are no repeating groups of data.
3) Ensure that there is a primary key.

a)First Rule of 1NF:

You must define the data items. This means looking at the data to be stored, organizing the data into columns,
defining what type of data each column contains, and finally putting related columns into their own table.
For example, you put all the columns relating to locations of meetings in the Location table, those relating
to members in the MemberDetails table, and so on.

b)Second Rule of 1NF:

The next step is ensuring that there are no repeating groups of data.

Consider we have following table:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), ORDERS VARCHAR(155) );

So if we populate this table for a single customer having multiple orders then it would be something as follows:

ID NAME AGE ADDRESS ORDERS 100 Sachin 36 Lower West Side Cannon XL-200 100 Sachin 36 Lower West Side
Battery XL-200 100 Sachin 36 Lower West Side Tripod Large But as per 1NF,

we need to ensure that there are no repeating groups of data. So let us break above table into to parts
and join them using a key as follows:

CUSTOMERS table:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25),
PRIMARY KEY (ID) );

This table would have following record:

ID NAME AGE ADDRESS 100 Sachin 36

Lower West Side ORDERS table:

CREATE TABLE ORDERS( ID INT NOT NULL, CUSTOMER_ID INT NOT NULL, ORDERS VARCHAR(155),
PRIMARY KEY (ID) );

This table would have following records:

ID CUSTOMER_ID ORDERS 10 100 Cannon XL-200 11 100 Battery XL-200 12 100

Third Rule of 1NF:
The final rule of the first normal form .
create a primary key for each table which we have already created. Second Normal Form Second normal
form states that it should meet all the rules for 1NF and there must be no partial dependences of any
of the columns on the primary key:Consider a customer-order relation and you want to store customer ID,
customer name, order ID and order
detail,and date of purchage:

CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, ORDER_ID INT NOT NULL,
ORDER_DETAIL VARCHAR (20) NOT NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) );

This table is in first normal form, in that it obeys all the rules of first normal form. In this table,
the primary key consists of CUST_ID and ORDER_ID. Combined they are unique assuming same
customer would hardly order same thing. However, the table is not in second normal form because
there are partial dependencies
of primary keys and columns. CUST_NAME is dependent on CUST_ID,
and there's no real link between a customer's name and what he purchaged.
Order detail and purchage date are also dependent on ORDER_ID, but they are not dependent on
CUST_ID,
because there's no link between a CUST_ID and an ORDER_DETAIL or their SALE_DATE. To make
this table comply
with second normal form, you need to separate the columns into three tables. First,

create a table to store the customer details as follows:

CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, PRIMARY KEY (CUST_ID) );

Next, create a table to store details of each order:

CREATE TABLE ORDERS( ORDER_ID INT NOT NULL, ORDER_DETAIL VARCHAR (20) NOT NULL, PRIMARY KEY (ORDER_ID) );

Finally, create a third table storing just CUST_ID and ORDER_ID to keep track of all the orders for a
customer:

CREATE TABLE CUSTMERORDERS( CUST_ID INT NOT NULL, ORDER_ID INT NOT NULL, SALE_DATE DATETIME, PRIMARY KEY
(CUST_ID, ORDER_ID) );

Third Normal Form A table is in third normal form when the following conditions are met:

It is in second normal form.
All nonprimary fields are dependent on the primary key. The dependency of nonprimary fields is
between the data.

For example in the below table, street name, city, and state are unbreakably bound to the zip code.

CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, STREET VARCHAR(200),
CITY VARCHAR(100), STATE VARCHAR(100), ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );

The dependency between zip code and address is called a transitive dependency.
To comply with third normal form, all you need to do is move the Street, City, and State fields into
their own table, which you can call the Zip Code table:

CREATE TABLE ADDRESS( ZIP VARCHAR(12), STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), PRIMARY KEY (ZIP) );

Next, alter the CUSTOMERS table as follows:

CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );

The advantages of removing transitive dependencies are mainly twofold. First,
the amount of data duplication is reduced and therefore your database becomes smaller.
The second advantage is data integrity. When duplicated data changes, there's a big risk of updating
only some of the data, especially if it's spread out in a number of different places in the database.

For example, If address and zip code data were stored in three or four different tables, then any
changes in zip codes would need to ripple out to every record in those three or four tables.


I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.


Friday 12 July 2013

basic Syntax in sql server

Hi ,

           SQL is followed by unique set of rules and guidelines called Syntax. This Article gives
you a quick start with SQL by listing all the basic SQL Syntax: All the SQL statements start
with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP,
CREATE, USE, SHOW and all the statements end with a semicolon (;). Important point
to be noted is that SQL is case insensitive which means SELECT and select have same meaning
in SQL statements but MySQL make difference in table names. So if you are working with
MySQL then you need to give table names as they exist in the database.


SELECT Statement:

SELECT column1, column2....columnN FROM table_name;

DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN FROM table_name;

WHERE Clause:

SELECT column1, column2....columnN FROM table_name WHERE CONDITION;

AND/OR Clause:

SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2;

IN Clause:

SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);

BETWEEN Clause:

SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;

Like :

SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };

ORDER BY :

SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};

GROUP BY :

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;

COUNT :

SELECT COUNT(column_name) FROM table_name WHERE CONDITION;

HAVING :

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);

CREATE TABLE Statement::

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );

DROP TABLE Statement::

DROP TABLE table_name;

CREATE INDEX :

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);

DROP INDEX :

ALTER TABLE table_name DROP INDEX index_name;

TRUNCATE :

TRUNCATE TABLE table_name;

ALTER :

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

Statement (Rename):

ALTER TABLE table_name RENAME TO new_table_name;

INSERT INTO :

INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);

UPDATE:

UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];

DELETE Statement:

DELETE FROM table_name WHERE {CONDITION};

CREATE DATABASE :

CREATE DATABASE database_name;

DROP :

DROP DATABASE database_name;

USE :

USE DATABASE database_name;



I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.


Related Posts

Group by in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Constraints in SQL

Hi,
                    In This artcle we will see about Constraints in sql indetails.

SQL Constraints:

Constraints are the rules enforced on data columns on table.These are used to limit the type of data that
can go into a table.This ensures the accuracy and reliability of the data in the database.
Contraints could be column level or table level. Column level constraints are applied only
to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL

NOT NULL Constraint:

Ensures that a column cannot have NULL value.

DEFAULT Constraint :

Provides a default value for a column when none is specified.

UNIQUE Constraint:

Ensures that all values in a column are different.

PRIMARY Key:

Uniquely identified each rows/records in a database table.

FOREIGN Key:

Uniquely identified a rows/records in any another database table.

CHECK Constraint:

The CHECK constraint ensures that all values in a column satisfy certain conditions.

INDEX:

Use to create and retrieve data from the database very quickly.

NOT NULL Constraint:

By default, a column can hold NULL values. If you do not want a column to have a NULL value then
you need to define such constraint on this column specifying that NULL is now not allowed for that column
. A NULL is not the same as no data, rather, it represents unknown data.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three
of which, ID and NAME and AGE, specify not to accept NULLs

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR
(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

DEFAULT Constraint:

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement
does not provide a specific value.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here SALARY column is set to 5000.00 by default, so in case INSERT INTPO statement
does not provide a value for this column then by default this
column would be set to 5000.00.

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) );

UNIQUE Constraint:

The UNIQUE Constraint prevents two records from having identical values in a particular column.

Example:

you might want to prevent two or more people from having identical age. For example, the following SQL
creates a new table called CUSTOMERS and adds five columns. Here AGE column is set to UNIQUE, so
that you can not have two records with same age:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

PRIMARY Key:

A primary key is a field in a table which uniquely identifies the each rows/records in a database table.
Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key which may consist of single or multiple fields.
When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s) then you can not have two records having the same value
of that field(s).

Example:

Note: You would use these concepts while creating database tables. Create Primary Here is the syntax to
define ID attribute as a primary key in a CUSTOMERS table.

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

FOREIGN Key:

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Primary key field from one table and insert it into the other table where it becomes a foreign key
ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the
second table. If a table has a primary key defined on any field(s) then you can not have two records having
the same value of that field(s).


Example:

Consider the structure of the two tables as follows:

CUSTOMERS table:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );


ORDERS table:

CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double, PRIMARY KEY (ID) );

CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates to false, the record violates the constraint and isn.t entered into the table.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18)
, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

select Group_by Comma Separated

Hi ,
         In this article we will see how to get a group by comma separated values sql server.

Check Below example ::

step1:: Create table and fill some data in that table.


Create table #Group_by_CommaSeprated(id int ,Hosp_name varchar(50),City varchar(50))
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'sandip','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'raj','pune')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'ajit','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'aditya','nashik')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rachit','bhopal')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rakesh','bihar')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'shrikant','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'chandan','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'dishal','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'saket','UP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'pranav','sangli')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'aditya','kolhapur')

step 2:: Check your data .

select * from #Group_by_CommaSeprated


Step 3:: Select Query for get a group by comma separated values sql server.


SELECT id, Hosp_name =
STUFF((SELECT ', ' + Hosp_name
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, ''), City =
STUFF((SELECT ', ' + City
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #Group_by_CommaSeprated a
GROUP BY id

Step 4:: OUTPUT



I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.


Related Posts

Group by in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Tuesday 9 July 2013

magic tables in sql server

   Hi ,
        In this article we will see magic table in details.

What is magic tables?

There are Inserted and Deleted logical tables in SQL Server.These tables are automatically created and managed
by SQL Server internally to hold recently inserted,deleted and updated values during DML operations
(Insert,Update,Delete) on a database table.

both inserted and deleted table called as magic tables.
Basically, logical tables are used by triggers.

A)Inserted logical Table::

The Inserted table holds the recently inserted or updated values means new data values.
Hence newly added and updated records are inserted into the Inserted table.

For example of Inserted logical Table .CLICK HERE

B)Deleted logical Table::

The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.

For example of Deleted logical Table . CLICK HERE

We could not create the logical tables or modify the data with in the logical tables.Except triggers,
When you use the OUTPUT clause in your query, logical tables are automatically created and managed
by SQL Server. OUTPUT clause also has access toInserted and Deleted logical tables just like triggers.

     I hope You got something useful in this article.I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.


Other Popular Posts ::

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update

deleted Magic table in sql server

Deleted logical Table::

                   The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.

Example::

   Suppose we have emp_info . Now We need to create two triggers to see data with in logical tables
Inserted and Deleted.

Step 1::Create Table

create table emp_info (empno int,ename varchar(100),manage_id int)

Step 2 ::CREATE TRIGGER on emp_info


CREATE TRIGGER trg_Emp_Ins
ON emp_info
FOR UPDATE
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end

Step 3::Now Update a record in emp_info table to see data with in deleted logical table.


update emp_info set ename='Sandip Gaikwad', manage_id=3 where empno=3
SELECT * FROM emp_info

Step 4::OUTPUT

Other Popular Posts ::

Inserted Magic table with INSERT statement In sql sever

Hi,
     In this article we will see example of logical table or magic table when we insert data in sql table.

Example::

Suppose we have emp_info . Now We need to create two triggers to see data with
in logical tables Inserted and Deleted.

Step 1::Create Table

create table emp_info (empno int,ename varchar(100),manage_id int)

Step 2 :: CREATE TRIGGER on emp_info


CREATE TRIGGER trg_Emp_Ins
ON emp_info
FOR INSERT
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end

Step 3::Now insert a new record in emp_info table to see data with in Inserted logical table.


INSERT INTO emp_info(empno, ename, manage_id) VALUES(3,'Sandip G',3)
SELECT * FROM emp_info


     I hope You got something useful in this article.I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.

Other Popular Posts ::

self join in sql sever

   
Self Join::


             A self join is a join in which a table is joined with itself,specially when the table has
a foreign key which references its own primary key.
         in other words self join means two instances of the same table will be joined
in the query.

for your better understanding Check below example.

Step 1 ::Create Table and fill some data in that table


   create table #emp_info (empno int,ename varchar(100),manage_id int)

  insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
  insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
  insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
  insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',1)
  insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)

Step 2 :: Query for self join

Select e.empno, e.ename, m.ename 'Manager' from #emp_info m, #emp_info e where e.manage_id =m.empno

in above query we are apply join on two instance e and m of #emp_info

Step 3 :: Output





Related Posts ::

1)               Joins in Sql server
2)               Interview queries in sql server
3)               SET ANSI_NULLS in sql server
4)               SET QUOTED_IDENTIFIER in sql
5)               Dynamic Query in sql server
6)               Basic About View
7)               send sql table data via email using store procedure

Swap values of two columns in sql server

Hi ,
        Suppose In case of data uploading or data inserting you done mistake to upload or insert
data of one column to other then you want to swap the values of that two columns of a table
in SQL Server.
Suppose you have a #emp_info table in the database with the following data and
you want to interchange the values of empno and then how do you do?

r
create table #emp_info (empno int,ename varchar(100),manage_id int)

insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',2)
insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)

Your Data ::

select * from #emp_info

Don't worry, to do this task, you need to just write a simple update query for Customer table like as :

UPDATE #emp_info SET empno=manage_id , manage_id=empno


Other Popular Posts ::

Insert value in Identity column of table

Hi,
                        In This article we will see how to insert value in Identity column.Identity field
is usually used as a primary key. When you insert a new record into your table,
this field automatically assign an incremented value from the previous entry.
Usually, you can't insert your own value to this field.
In this article, I am going to expose the tips for inserting your own value to this field.
It is simple and easy. Consider you have the following Emp_Info table.


CREATE TABLE Emp_Info
(
ID int IDENTITY,
Emp_Name varchar(100),
Emp_Address varchar(200)
)

Now, I am trying to insert a record into Emp_Info table with
identity field like as then I will get the error message as shown below.

INSERT INTO Emp_Info(ID,Emp_Name,Emp_Address) VALUES(1,'Ajit Gaikwad','sangli')

Error::

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Emp_Info' when IDENTITY_INSERT is set to OFF.

A) Allow insert into identity field

You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular
table as shown:

SET IDENTITY_INSERT Emp_Info ON

B) Disallow insert into identity field

You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a
particular table as shown:

SET IDENTITY_INSERT Emp_Info OFF

Test:: Insert Value to Identity field

Now, lets see how to insert our own values to identity field ID with in the Emp_Info table.


SET IDENTITY_INSERT Emp_Info ON
INSERT INTO Emp_Info(ID,Name,Address) VALUES(3,'harshal','sangli')
INSERT INTO Emp_Info(ID,Name,Address) VALUES(4,'ram','sangli')

SET IDENTITY_INSERT Emp_Info OFF
INSERT INTO Emp_Info(Name,Address) VALUES('rakesh','bihar')
After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.


Other Popular Posts ::

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update

Saturday 6 July 2013

Check all posts

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server

Create html table by using sql store procedure

          Hi ,
                                    In this article we will see how to create html table inside html table
by using sql server to show data of select statement.
step 1:: Create table


CREATE TABLE #TEMP (EMP_NAME VARCHAR(100),CHILD_NAME VARCHAR(100),CHILD_HOBBY VARCHAR(100),CHILD_AGE INT)

step 2:: Insert data in table


insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','CRI',2)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','READ',2)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','CRI',4)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','READ',4)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANds','TE',24)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANsd','ME',42)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','JI',23)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','CI',23)


step 3:: Use below query for select data and create html table inside html table.

 

step 4::Output

select column data as row by using sql server

Hi,
In this article we will discuss about how to select or display column data as row by using sql server.
1) Create table


CREATE TABLE YourTable
(RowID int primary key not null identity(1,1)
,emp_id int null
,emp_name varchar(100)
,Last_name varchar(100)
,salary money
,StatusValue char(1)
,ranks int
)

2) Insert Records in to table


INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (1,'sandip','Gaikwad',1234.56,'A',12)
INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (2,'pranav','Gaikwad',14034.56,'I',102)

3) Check origanal data



3) Check below query to select column data as row.

DECLARE @YourTableName varchar(1000)
DECLARE @YourTableWhere varchar(1000)
DECLARE @YourQuery varchar(max)
SET @YourTableName='YourTable'
set @YourTableWhere='y.RowID=1'
SELECT
@YourQuery = STUFF(
(SELECT +
' UNION '
+ 'SELECT '''+COLUMN_NAME+''' as Col_name, CONVERT(varchar(max) ,'+COLUMN_NAME+') as column_data FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
FOR XML PATH('')
), 1, 7, ''
)

4) Check output ::

EXEC (@YourQuery)

pivot with dynamic column names in sql sever

Hi ,
               In previous article we see how to pivot data with static column name. now we will
discuss about how to do privot data with dynamic column name. for your better understanding
create below table and fill it with some data.

Create table ::

create table #color (Color_id int,Color_name varchar(50),color_prize int)

Insert Data::


insert into #color (Color_id,Color_name,color_prize) values (1,'black',1)
insert into #color (Color_id,Color_name,color_prize) values (1,'red',2)
insert into #color (Color_id,Color_name,color_prize) values (1,'wt',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'re',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'Grey',4)

insert into #color (Color_id,Color_name,color_prize) values (2,'black',6)
insert into #color (Color_id,Color_name,color_prize) values (2,'red',7)
insert into #color (Color_id,Color_name,color_prize) values (2,'wt',8)
insert into #color (Color_id,Color_name,color_prize) values (2,'re',9)
insert into #color (Color_id,Color_name,color_prize) values (2,'Grey',10)


get All column as comma separated string::

declare @Dynamic_column varchar(max)
select @Dynamic_column = COALESCE(@Dynamic_column+',' , '') +Color_name from (select distinct Color_name from #color)t
print @Dynamic_column

write a dynamic Query for pivot with dynamic columns::


declare @query varchar(max)
set @query ='select * from
(
select Color_id,Color_name,color_prize from #color
)te
pivot
(
min(color_prize) for Color_name in('+@Dynamic_column +')
) as d'

exec(@query)