SQL bulk insert sample

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

BULK
INSERT CSVTest
FROM 'F:\naiki_DB\mike\cvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

avoid duplicates in insert statement of SQL

/*

Create a table variable to store the data you want to insert,

this will be used to do validation when inserting to the actual table

*/

declare @test_table table

(

test_description varchar(50)

)

/*

Insert data into the variable(temp) table,

It is best to use union select to ad more lines.

Or data can be selected from another source, eg: select Description from [other_table]

*/

insert into @test_table

select

'Description 14'

union

select

'test description 24'

union

select

'test description 34'

union

select

'Description 16'

/*

Insert the data into the physical table using a join,

This is to avoid duplicates, and the statement below can stay as is.

and only modify the temp table. so there is only one point of data entry into the physical tables,

with a proper check.

*/

insert into test_table (test_description)

select

new.test_description 'test_description'

from

test_table existing
right join

@test_table new
on

(

new.test_description = existing.test_description

)

where

existing.test_description is null

select * from test_table