BCP Command

      Using Bulk Copy Program (BCP) Utility, quickly we import and export large amounts of data. We can run the BCP command from the command prompt.

      Here i am going to explain, how to import data from a flat file to a data base table.

Source File:
File Path: D:\Programs\Product.txt

Target table:
ServerName      : KiranY
UserName        : sa
Password          : 123
DataBaseName : Sample

Table Name: PRODUCT

Table Creation Script:
          First we need to load source data into staging table. The stg table column data type should be character type. While the time of target table load (from stg table) we can convert to required data types.

                        CREATE TABLE STG_PRODUCT
                        (
                        PID VARCHAR(50)
                        ,PNAME VARCHAR(50)
                        ,PRICE VARCHAR(50)
                        )

BCP Command:
Syntax:
bcp <datbasename>..<tablename> in <pathname of the file with extension> -S<servername> -U<username> -P<password> -C -t"<column delimiter>" -r<row delimiter>

Command:
 bcp SAMPLE..STG_PRODUCT in D:\Programs\Product.txt -SKIRANY -Usa -P123 -c -t"," -r\n

Output:

Joins

          Join combines records from two or more tables in a database. Below are the different types of joins

Types of Joins:

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Self Join

Cross Join

          Cross join produces the Cartesian product of the two tables (left and right). This cross join does not have a Where clause. This product result set is the number of rows in the first table multiplied by the number of rows in the second table.

If EMP table is having 5 records and table DEPT is having 3 records. In Cross Join output will come as 15 records.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            CROSS JOIN DEPT B

Self Join

      A table can be joined to itself in a self join. In self join left and right tables are same with different alias name.If we need to create a result set that joins records in a table with other records in the same table, then we can use self join.

Example: I need to display all employee name along with their manager name.
Sample Emp table:
E_ID    DEPT_ID    E_NAME    MANAGER_ID
1          10                A                 NULL
2          20                B                 1
3          30                C                 2


Query:
       SELECT
          a1.E_NAME
           ,b2.E_NAME AS MANAGER_NAME
          FROM EMP a1
          LEFT JOIN EMP b2
          ON a1.MANAGER_ID = b2.E_ID

Output:

Outer Join

         It returns matched and unmatched records from both (Left and Right) tables based on given Join condition. These are three types.

• Left Outer Join
• Right Outer Join
• Full Outer Join

Left Outer Join: 
        It returns all records from left table and only matched records from right table. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            LEFT OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

Right Outer Join: 
        It returns all records from right table and only matched records from left table. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            RIGHT OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

Full Outer Join: 
        It returns all matched and unmatched records from both the tables. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            FULL OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

Inner Join

       It returns all matched records from both (Left and right) tables, based on given condition.

Example: Below three queries return same result.

    1. Using on condition:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            INNER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID
 
      2. Using Where condition:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A,
            DEPT B
            WHERE A.Dept_ID = B.Dept_ID

      3. We can  mention 'JOIN' instead of  'INNER JOIN'.
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

DEFAULT CONSTRAINT

            Using default constraint we can insert default value into a column, if no other value is specified for that particular column.

Applying constraint on a new table:

Syntax:

CREATE TABLE <Table name>

(

<Col 1> <Data type>,

<Col 2> <Data type>,

.

.

<Col n> <Data type> DEFAULT <Value>

)

Example:

CREATE TABLE DEFAULT_TB1

(

ID INT

,Name varchar(10) DEFAULT 'aa'

)

Data insertion:

Inserting value to the ‘Name’ column:

If we specified a value for name column then same value will be inserted.

INSERT INTO DEFAULT_TB1 (ID,Name) VALUES (102,'ab')

O/P: SELECT * FROM DEFAULT_TB1

Inserting default value to the ‘Name’ column:

If we are not specified a value for name column then default value will be inserted.

INSERT INTO DEFAULT_TB1 (ID) VALUES (102)

O/P: SELECT * FROM DEFAULT_TB1

Applying constraint on existing table:

Syntax:

ALTER TABLE <Table name> add constraint <Constraint name> DEFAUTL <Default value> FOR <Column name>

Example:

ALTER TABLE DEFAULT_TB2 ADD CONSTRAINT DFC_Name2 DEFAULT 'AA' FOR Name

O/P: SP_HELP DEFAULT_TB2

Drop constraint:

ALTER TABLE DEFAULT_TB DROP CONSTRAINT DFC_Name2