Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

This is very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202



Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest


http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Isolation Levels in the Database Engine

The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:

*

Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
*

Read committed (Database Engine default level)
*

Repeatable read
*

Serializable (the highest level, where transactions are completely isolated from one another)

SELECT top 10 records

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10

Sybase

SET rowcount 10
SELECT column FROM table

Firebird

SELECT FIRST 10 column
FROM table

Different Types of VIEW

There are two different types of VIEW

* System View
o Information Schema View
o Catalog View
o Dynamic Management View(DMV)
* User Define View
o Simple View
o Complex View


In SQL Server all system view are divided into different schema. These are used for the security container of SQL Server database. We can categorized system view in following way,

* Information Schema View
* Catalog View
* Dynamic Management View (DMV)

Now all above category are itself a huge topic, So I will not going to Details of It. Lets have a look into the over view of those view type

Information View

These are the one of the most important system grouped view. There are twenty different schema views in this group. This are used for displaying most physical information of a database, such as table, columns. Naming Conation of this type of views are INFORMATION_SCHEMA.[View Name] . From the System View Image we can get the few name of Information Schema View.

Lets see it with one Example,

I have create on Database named, ViewDemo. It having on table called EmpInfo and below diagram showing you the design of the table,
Now, if we want to know the details information columns of table Empinfo using View we have to run the following query,



SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='EmpInfo'



Similarly we can use all other Schema View to read Database information.

Catalog View

This type of view are introduced in SQL Server 2005. catalog view are categorized in different group also. These are used to show the database self describing information.

As for example,
Collapse

select * from sys.tables



Dynamic Management View

This is newly introduced in SQL Server 2005.This Views gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMV

1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database

As for example if we want to check the all SQL Server connection, we have to use following query,
Collapse

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

http://www.codeproject.com/KB/database/View.aspx

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/viewsinsqlserver200510132009103348AM/viewsinsqlserver2005.aspx

FIND nth RECORD USING CURSOR

declare sc cursor static for select * from system1.dbo.sys_params

declare @n int
open sc
set @n=25
while (@n<=29)
begin
fetch absolute @n from sc

set @n=@n +1
end
close sc
deallocate sc

Find Nth maximum,minimum value in SQL Server

By Guest Authors on 11 April 2004 | 14 Comments | Tags: SELECT

This aritlce is written by Hariharan Velayuthan. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."

[Note: This article assumes that the reader is familiar with the T-SQL, joins and queries]

I have taken utmost effort to make this article easy to understand, but incase you are not clear with the concept, please raise up your concern and I’ll be more than happy to attend your doubts. All the examples discussed in this article uses Employee table. If you do not have this table, please use the following script to create it.

Use Pubs
Go

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go

Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go

A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)

How does this query work?

The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:

Select * from Employee where salary = (7500)

Returns:

Eid Name Salary
5 steven 7500



find nth maximum salary

Select * From Employee E1 Where
(n-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

find nth minimum salary

Select * From Employee E1 Where
((select Count(Distinct(Salary)) from Employee) -n) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

delete duplicate records using Identitiy

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)

Duplicate delete all rows in table

with CTE(COL1,COL2,DUPLICATECOUNT
AS(
select COL1,COL2,ROW_NUMBER() OVER(partition by COL1,COL2 order by COl1)as DUPLICATECOUNT from TABELNAME
)
DELETE from CTE WHERE DUPLICATECOUNT >1

Can you insert into views that are based on joins?

it is possible, provided that your insert statement has a column list that makes it clear there's only one table involved.

You cannot insert into multiple tables with a single insert on a view.

How To Found Current Identity No

this is any tabel we get currnt identity
synatax: select ident_current('TABELNAME')
select ident_current('system1k.dbo.menu')


and SELECT @@IDENTITY;
this is used only when ever user insert rows using query then uwrite blow which is the identity then u use this statement

ex:INSERT INTO menu values( 'ggdg','dgytdytd','true','true',6,'tyu')
SELECT @@IDENTITY;

then display the current IDENTITY value

USE TempDB
GO
CREATE TABLE tst
( a int identity(1,1), s varchar(10))
GO
USE TempDB

CREATE TABLE tst2
( a int identity(1000,1), s varchar(10))
USE TempDB
GO
CREATE TRIGGER dbo.trgTst
ON tst
AFTER INSERT
AS INSERT tst2 SELECT inserted.s FROM inserted
GO
INSERT tst VALUES('a')
SELECT
@@IDENTITY AS [@@IDENTITY],
SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
GO
DROP TABLE tst2
DROP TABLE tst