How to import data directly from T-SQL commands without using SQL Server Integration Services?

There are so many ways to import data directly using T-SQL.

Here listed couple of commands which helps to load the Data quickly into the Database:

• BCP
• Bulk Insert
• OpenRowSet
• OPENDATASOURCE
• OPENQUERY

How to insert the values manually for the Identity Column ?

What is Identity Column :

•  For Identity Column, SQL Server will provide the values automatically at the time of inserting each record in the table.

Example :

-- Creating the table

CREATE TABLE TestTable ( TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TestValue NVARCHAR(50) NOT NULL )

-- To insert value to the identity column

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')

-- Cannot insert explicit value for identity column in table 'TestTable' when IDENTITY_INSERT is set to OFF.


SET IDENTITY_INSERT TestTable ON       -- Disable the Identity to Insert the values manually

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')       -- Insert the values for the identity column

select * from TestTable

SET IDENTITY_INSERT TestTable OFF    -- Enable the Identity to Insert the values by the Server automatically

Differences between Stored procedures and User defined functions

Difference 1:

• Stored procedure used for perform specific tasks
• Normally functions will be used for computing value

Difference 2: 

• Stored procedures may or may not return values
•  But function should return value

Difference 3:

• Stored procedure cannot be used in the select/where/having clause
• But function can be called from select/where/having clause 

Difference 4:

• Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

• But function cannot run independently, It has to be the part of the SQL statement

Difference 5:

• Temporary table (derived) cannot be created on function
• But it can be created in stored procedures 

Difference 6:

• From sql server 2005 onwards, TRY CATCH statements can be used in the stored procedures

• But it cannot be used in the function. But we can use raise error function

Difference 7:

• Stored procedure can call the user defined functions
• But the function cannot call the stored procedures

The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server.

• System Stored procedure
• User defined Stored procedure
• NET CLR stored procedure
• Extended stored procedure

Except extended stored procedures no one can call the user defined functions

Difference 8:

• Stored procedures can have input and output parameters
• But the function can have only input parameters

Difference 9:

• Stored procedures can have select and all DML operations
• But the function can do only select operation

Functions will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions

Difference 10:

• Function cannot have the transaction statements.

The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function.

• Stored procedure can use transaction statements

DISTINCT

Distinct statement eliminates the duplicate data in the specified column from the table.

In a table, some of the columns may contain duplicate values. Sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.
 
DISTINCT Syntax:-
 
SELECT DISTINCT <Column_Name> FROM <Table_Name>
 
DISTINCT Example:-
 
We have a table called “salestaxRate” and columns are SalesTaxRateID,  TaxType, TaxRate, Name, ModifiedDate  as shown below...

Now we want to select only the distinct values (Different Values) from the column named "TaxRate" from the table above.

We use the following SELECT statement:

SELECT DISTINCT TaxRate FROM salestaxRate

The result-set will look like this