Thursday, 31 October 2013

How to CREATE a Copy or Duplicate Table of another Table in SQL

SQL Query to Copy a Table Structure from another Table

Method 1 : INSERT INTO SELECT

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are of same data type, they are are not required to list them. I always list them for readability and scalability purpose.

Copying Specific subset of columns into an Existing Table from another Existing Table : 

CREATE TABLE SampleTable(PID Int,FirstName VARCHAR(100), LastNameVARCHAR(100))

INSERT INTO 
SampleTable(PID,FirstName, LastName) SELECT PNo FName, LName FROM Persons. 


Notes :
Here the columns names may different but the data type should be same.

Copying all columns into an Existing Table from another Existing Table : 
To do this bothe tables should have same no.of columns and same data type.

INSERT INTO SampleTable SELECT * FROM Persons. 

Method 2 : SELECT INTO 

This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.


The easiest way to create a copy of a table is to use a Transact-SQL command SELECT INTO to extract all the rows from an existing table into the new table. The new table must not exist already. The following example will copy the Customers table under the Sales Schema to a new table called NewCustomers under the TPR Schema:


We can create a Table from an existing Table by copying the existing Table's Columns.
It is important to note that when creating a Table in this way, the New Table will be populated with the records from the existing Table (based on the SQL SELECT Statement).

SELECT * INTO TPR.NewCustomers FROM Sales.Customers;

Copying Specific subset of columns from Existing Table to New Table
You can also create the new table from a specific subset of columns in the original table. In this case, you specify the names of the columns to copy after the SELECT keyword. Any columns not specified are excluded from the new table. The following example copies specific columns to a new table:

SELECT CustName, Address, Telephone, Email INTO TPR.NewCustomers

FROM Sales.Customers;

Copying only Table Structure from Existing Table to New Table :
Creating a duplicate table with only the structure duplicated with a new name as follows :
SELECT * INTO TPR.NewCustomers FROM Sales.Customers where 1=2;

Oracle/PL-SQL :

Copying all Columns from another Table
The syntax for CREATING a Table by copying all Columns from another Table is:
CREATE TABLE New_Table
  AS (SELECT * FROM Old_Table);
For Example:
CREATE TABLE Vendors
AS (SELECT *
    FROM Customers
    WHERE id > 1000);
This would create a New Table called Vendors that included all Columns from the Customers Table.
If there were records in the Customers Table, then the New Vendors Table would also contain the records selected by the SELECT statement.

Copying selected Columns from another Table
The syntax for CREATING a Table by copying selected Columns from another Table is:
CREATE TABLE New_Table
  AS (SELECT Column_1, Column2, ... Column_n FROM Old_Table);
For Example:
CREATE TABLE Vendors
  AS (SELECT id, address, city, state, zip
      FROM Customers
      WHERE id > 1000);
This would create a New Table called Vendors, but the New Table would only include the specified Columns from the Customers Table.
Again, if there were records in the Customers Table, then the New Vendors Table would also contain the records selected by the SELECT statement.

Copying selected Columns from multiple Tables
The syntax for CREATING a Table by copying selected Columns from multiple Tables is:
CREATE TABLE New_Table
  AS (SELECT Column_1, Column2, ... Column_n
      FROM Old_Table_1, Old_Table_2, ... Old_Table_n);
For Example:
CREATE TABLE Vendors
  AS (SELECT Customers.id, Customers.address, Categories.cat_type
      FROM Customers, Categories
      WHERE Customers.id = Categories.id
      AND Customers.id > 1000);
This would create a New Table called Vendors based on Columns from both the Customers and Categories Tables.

F A Qs :
Question: How can I create an SQL Table from another Table without copying any values from the Old Table?
Answer: To do this, the syntax is:
CREATE TABLE New_Table
  AS (SELECT * FROM Old_Table WHERE 1=2);
For Example:
CREATE TABLE Vendors
  AS (SELECT * FROM Customers WHERE 1=2);


This would create a New Table called Vendors that included all Columns from the Customers Table, but no data from the CustomersTable

Wednesday, 23 October 2013

What is the Difference between SQL Server Inner Join and Outer Join

SQL SERVER JOIN Clause :
The SQL JOIN is a clause that enables a SELECT statement to access more than one table. The JOIN clause controls how tables are linked. It is a qualifier of the SQL FROM clause.
In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use Primary Key of first table and Foreign Key of secondary table to get data from tables, by using this relationship we can reduce the duplication of data in every table.

Why and How to use JOIN Clause :
Use the SQL JOIN whenever multiple tables must be accessed through a SQL SELECT statement and no results should be returned if there is not a match between the Joined tables.The ON clause describes the conditions of the JOIN.

Syntax :
SELECT <column_name1>, <column_name2> <aggregate_function> FROM <table_name> JOIN <table_name> ON <join_conditions>

Example : 
Let us suppose there are two tables called CUSTOMERS and ORDERS as follows

Customers Table 
CREATE TABLE Customers(Cid INT PRIMARY KEY IDENTITY(111,1), 
Cname VARCHAR(15), Location VARCHAR(15));

INSERT INTO Customers VALUES ('Abc','Hyderabad'),('Bcd','Bangalore'),('Cde','Chennai') ,('Efg','Pune'), ('Fgh','Hyderabad'),('Ghi','Bangalore'),('Ijk','Bangalore');

Select * From Customers  



Orders Table
CREATE TABLE Orders(Oid INT IDENTITY(1234,3),Prouduct VARCHAR(15),Cid INT);

INSERT INTO Orders VALUES('LapTop',113),('TeleVision',115),('Cooler',117),
('Fridge',143),('Pen Drive',789);

Select * From Orders

Now we perform the join on the above tables as follows 
SELECT C.Cid,C.Cname,C.Location,O.Oid,O.Product FROM 
CUSTOMERS C JOIN Orders O ON C.Cid=O.C_Id ;

This join is also known as Normal Join or Inner Join.The result of the Join is as follows :



Inner Join Vs Outer Join
The standard JOIN clause (also known as the INNER JOIN clause) returns the rows only when there are matches for the JOIN criteria on the second table.If there are no matches on the JOIN criteria then no rows will be returned. 

An INNER JOIN should be used only when you want to pull data that exists in both of the tables. If any entry found in the first table, and it does not have a matching entry in the second table, then that record will not be included in the result set.

An OUTER JOIN should be used when you want to pull all of the matching data in the first table, regardless of whether or not it exists in the second table. Your result set will contain NULL for the rows that don't have the matching records in the second table.

Notes :
A "Cartesian product" can result if there is no relation between the tables for the join. A row would be included for each combination between the two tables so if one table has 1,000 rows and the second table has 2,000 rows then 2,000,000 rows would be returned.

Tuesday, 22 October 2013

How to use the Alter Command to Modify a Table Structure in SQL Server

SQL Server Alter Command
Alter is DDL command is used to modify the structure of the schema after the creation.
Suppose we have an Employee table with three columns Emp_id, Emp_name, Emp_salery. Now let's use the alter commands to modify the Employee table structure.

By using Alter command we can do the following operation in SQL Server:
Add Column - Adds a column to a table
Add Primary Key - Adds a primary key to a table
Add Foreign Key - Adds a foreign key to a table
Add Constraint - Adds a check constraint to a table
Change Column Type - Changes the data type of a column
Drop Column - Drops a column from a table
Drop Constraint - Drops a check constraint from a table
Drop Primary Key - Drops a primary key from a table
Drop Foreign Key - Drops a foreign key from a table
Rename Table - Renames a table
Rename Column - Renames a column

Examples :
Adding a Primary Key Constraint on an existing Column
ALTER TABLE Employee  ADD PRIMARY KEY(Emp_id) 
or
ALTER TABLE Employee  ADD CONSTRAINT Emp_id_pk PRIMARY KEY(Emp_id) 

Droping a Constrint
ALTER TABLE Employee DROP CONSTRAINT Emp_id_pk;

Adding a Foreign Key Constraint on an existing Column
Let us suppose Orders and Employee are two tables…
ALTER TABLE Orders  ADD FOREIGN KEY (Emp_id) References Employee(Emp_id)
or
ALTER TABLE Orders  ADD CONSTRAINT Emp_id_fk FOREIGN KEY (Emp_id) References Employee(Emp_id)

Increasing or Decreasing the size of an existing column
ALTER TABLE Employee  ALTER COLUMN Emp_name varchar(100) 

Changing the Data Type of an existing Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) 

Adding  a NULL Constraint on Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NULL 

Adding a NOT NULL Constraint on an existing Column
ALTER TABLE Employee  ALTER COLUMN Emp_name nvarchar(50) NOT NULL 

Adding a new Column on Table without any Constraint
ALTER TABLE Employee  ADD Emp_age varchar(10) 

Add a new Column on Table with a Constraint
ALTER TABLE Employee  ADD Emp_code varchar(100) CONSTRAINT Emp_code_uq UNIQUE 

Droping a Column from Table
ALTER TABLE Employee DROP COLUMN Emp_code
 
Adding a Check Constraint Column
ALTER TABLE Employee ADD CONSTRAINT Emp_age_ck CHECK(Emp_age > 18) 

Notes :
PRIMARY KEY CONSTRAINT cannot be altered, you may only drop it and create again. For big datasets it can cause a long run time and thus - table inavailability.


Thanks, Tamatam

SQL SP_HELP and SP_COLUMNS Stored Procedures to View,Define and Describe the Structure of a Table

SQL Server- SP_HELP and SP_COLUMNS Stored Procedures to Describe Table Structure 
We can use the SP_HELP and SP_COLUMNS stored procedures define the structure of a Table in SQL-Server.

SP_COLUMNS :
This stored procedure returns column information for the specified objects that can be queried in the current environment.

Example :
CREATE TABLE ORDERS(OID INT NOT NULL,CNAME CHAR(15),PRODUCT VARCHAR(15),P_COST DECIMAL(10,2));

The  SP_COLUMNS Procedure Describes the Table like this ... :
SP_Columns ORDERS




SP_HELP:

Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft® SQL Server™.

Example :
CREATE TABLE ORDERS(OID INT NOT NULL,CNAME CHAR(15),PRODUCT VARCHAR(15),P_COST DECIMAL(10,2));


The  SP_HELP Procedure Describes the Table like this ...:
SP_HELP ORDERS





Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts