SQL – Bulk Insert – Import Data From Flat File

 

The following Bulk Insert command Imports data from a file called FOO_DATA.csv into an SQL table called FOO_DATA.

Firstly, the Table FOO_DATA needs to cleared of its data:

TRUNCATE TABLE orders.dbo.FOO_DATA

The truncate table command deletes all data within a table and cannot be undone (Unless you can restore from backup), so use carefully. Table formats , indexes etc will remain intact.

A simple Bulk Insert command is as follows:

BULK INSERT orders.dbo.FOO_DATA

from ‘C:\reports\FOO_DATA.csv’
WITH ( FIELDTERMINATOR = ‘,’ )

The table FOO_DATA and the file FOO_DATA.CSV must be in the same field format. In this example FOO_DATA.csv is a comma separated file with no quotes.

You can import from a remote location by changing the file location:

BULK INSERT orders.dbo.FOO_DATA

from ‘\\ROB-PC\files\FOO_DATA.CSV’
WITH ( FIELDTERMINATOR = ‘,’ )

The account under which the SQL Services run must have access to this location.

Advertisements

SQL – Output Data from SQL Server To File – BCP

The following BCP command selects data from a table called CUSTOMERS in the DATA database and exports it to a file called FOO_REPORT.CSV:


declare
@bcpcommand           varchar(1000)

 

SET @bcpcommand = ‘bcp “SELECT id, title, forename, surname, product FROM DATA.dbo.customers where product = ”FOO”” queryout C:\reports\FOO_REPORT.CSV -c -T”‘

EXEC master..xp_cmdshell @bcpcommand

 You can also output to .XLS and .TXT.

How To Get The Current Date In SQL Server

The following code returns the current date:

 

— VARIABLE DECLARATION

declare @date as nvarchar(50)

declare @day as nvarchar(50)

declare @month as nvarchar(50)

declare @year as nvarchar(50)

declare @zeroday as nvarchar(50)

declare @zeromonth as nvarchar(50)

 

 

— SET THE DATE

set @date         = cast(getDate() as char)

set @day          = day(@date)

if @day < 10

      set @zeroday = 0

else

      set @zeroday =

set @month  = month(@date)

if @month < 10

      set @zeromonth = 0

else

      set @zeromonth =

set @year         = right(year(@date),2)

set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + @year

 

print @date

 

Part 1: Declare the variables to held the parts of the date.
Part 2: Set the key parts of the date into the variables. If the day or month is less than 10, then add a zero to the output.
e.g. If the date is 1st January 2009 we want the date to be displayed as 01/01/09 instead of 1/1/09.

Part 3: Set @date to the format required.
The following line specified the format of the date output.

set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + @year

This will outout the date as DD/MM/YY.

if you want the format MM/DD/YY use the following format:

set @date   = @zeromonth + @month + ‘/’ + @zeroday + @day + ‘/’ + @year

You can change the delimiter of course, to display DD-MM-YY replace the ‘/’ with ‘-‘.