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

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 both 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 Orders Schema:

SELECT * INTO Orders.NewCustomers FROM Sales.Customers;

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).

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 Orders.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 following query will 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.

CREATE TABLE Vendors
AS (SELECT *
FROM Customers
WHERE id > 1000);

Copying selected Columns from another Table :
The following query will 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.

CREATE TABLE Vendors
AS (SELECT id, address, city, state, zip
FROM Customers
WHERE id > 1000);

Copying selected Columns from multiple Tables :

The following query will create a New Table called Vendors based on Columns from both the Customers and Categories Tables.

CREATE TABLE Vendors
AS (SELECT Customers.id, Customers.address, Categories.cat_type
FROM Customers, Categories
WHERE Customers.id = Categories.id
AND Customers.id > 1000);

Creating a SQL Table from another Table without copying values but the Structure :

The following query will create a New Table called Vendors that included all Columns from the Customers Table, but no data from the Customers Table

CREATE TABLE Vendors
AS (SELECT * FROM Customers WHERE 1=2);

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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

What is the difference between Inner Join and Outer Join in SQL Server

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 will 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.

Thanks, Tamatam

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

How to Modify the Table Structure using Alter Statement in SQL Server
Alter is DDL command is used to modify the structure of the schema after the creation.
Scenario :

Suppose we have an Employee table with 3 Columns Emp_Id, Emp_Name, Emp_Salary. Now let's use the alter commands to modify the Employee table structure.

By using Alter command we can do the following operations 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 :

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) 

Thanks, Tamatam

How to use SP_HELP and SP_COLUMNS Stored Procedures in SQL Server

How to use SP_HELP and SP_COLUMNS Stored Procedures to View, Define and Describe the Structure of a Table in SQL Server
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 ... :

Exec 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 ...:

Exec SP_HELP ORDERS

Thanks, Tamatam

How to Fix the SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

SQL Arithmetic Overflow Error Converting Varchar to Data Type Numeric

The Arithmetic Overflow Error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable or column is not long enough to hold the decimal or numeric value.

Example Query :

CREATE TABLE CARS(CID INT NOT NULL, CNAME VARCHAR(15),
CCOST DECIMAL(5,4) DEFAULT '15000.1234');

INSERT INTO CARS(CID,CNAME) VALUES(123,'BOLERO');
SELECT*FROM CARS;

--DROP TABLE CARS;

Error Message :

Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting varchar to data type numeric.

The statement has been terminated.

Solution:

Numeric data types that have fixed precision and scale decimal [(p[,s])] and numeric[(p[,s])].

When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. 

The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision) :

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. 

The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) :

The no. of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. 

Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Precision                          Storage bytes

1 - 9                                           5

10-19                                         9

20-28                                        13

29-38                                        17

Converting decimal and numeric Data :

For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.
When converting float or real values to decimal or numeric, the decimal value will never have more than 17 decimals. Any float value < 5E-18 will always convert as 0. 

Resolved Query :

CREATE TABLE CARS(CID INT NOT NULL,CNAME VARCHAR(15),

CCOST DECIMAL(9,4) DEFAULT '15000.1234');

INSERT INTO CARS(CID,CNAME) VALUES(123,'BOLERO');

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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

How to use Self Join in SQL Server

SQL Server Self Join 
Joining the table to itself is called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table.
Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.
A self join could be an outer join or an inner join,depends on the requirement and how the query is written.

CREATE TABLE Employee(EId INT,Ename VARCHAR(15),Location VARCHAR(15));

INSERT INTO Employee VALUES(1,'Ravi','Hyderabad'),

(2,'Rani','Bangalore'),(3,'Johnny','Bangalore'),(4,'Mikel','Hyderabad'),

(5,'Lisa','Bangalore'),(6,'Ravi','Bangalore');

SELECT*FROM Employee;

Simple Query to display Employee details located in Hyderabad.

SELECT Eid,Ename,Location FROM Employee WHERE Location='Hyderabad';

Nested Query[Sub Query] to disply Employee details located  in Hyderabd.

SELECT EId,Ename,Location FROM Employee WHERE Location IN

(SELECT Location FROM Employee WHERE Ename='Mikel');

SELF JOIN to disply Employee details located in Hyderabad.

Joining the table to itself is called self join.Here in this example we join the table Employee as E1 to its alias E2.,the E1 and E2 are the Alias names of the same table.

SELECT E1.EId,E1.Ename,E1.Location FROM Employee E1,Employee E2

WHERE E1.Location=E2.Location AND E2.Ename='Mikel';

Self Join Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------