jump to navigation

Using the BULK INSERT statement to insert rows in a table with triggers and constraints March 26, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: ,
add a comment

In this post I would like to talk about how to import data (from a text file) using the BULK INSERT statement.

If you want to find out more about BULK INSERT have a look here .

I will also explain how the BULK INSERT statement works with tables that have constraints and triggers attached on them. If you want to have a look in similar post in my blog regarding the bcp utility click  here.

The general statement for BULK INSERT is

BULK INSERT  tablename FROM datafile (with options)

I will proceed with a hands on example.We will need a database to work with. I will use AdventureWorks2008R2. You can download it here .
We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.I will be using the Sales.Currency table. I will be using bcp to extract data from this table to a text file.Then I will create another table Sales.Currency1 in the same database and I will import the data from the text file using BULK INSERT statement  inside this new table.

1) In a new command window type (Start–>Run and type cmd)

bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -T -c

That will export all rows from the table in the file Currency.dat file.

2) Now I am going to create the new table


USE AdventureWorks2008R2 ;
GO
SELECT  *
INTO    AdventureWorks2008R2.Sales.Currency1
FROM    AdventureWorks2008R2.Sales.Currency
WHERE   1 = 2

3) Now we need in the same command window to type

bcp AdventureWorks2008R2.Sales.Currency1 in Currency.dat -T –c

and insert the data in the Currency1 table.You will see that the data is in the table by executing a simple “SELECT * FROM Sales.Currency1″

4) We will delete the data in the table by typing “DELETE Sales.Currency1″ in the query window.

Now we will alter the definition of the table by adding a constraint and then use the bcp command to insert the rows again in the Currency1 table.We will add a constraint that does not allow in the Currency1 table to enter values that have less than 5 characters in the Name field of the table.Type the following T-SQL statement in a new query window.


USE AdventureWorks2008R2 ;
GO
ALTER TABLE [Sales].[Currency1]  WITH NOCHECK ADD  CONSTRAINT [CK_Currency1] CHECK  ((len([name])>(4)))
GO

ALTER TABLE [Sales].[Currency1] CHECK CONSTRAINT [CK_Currency1]
GO

5) We can now try to insert (our table is empty) a new row that will violate the constraint.


USE AdventureWorks2008R2 ;
GO
INSERT INTO sales.Currency1
        ( CurrencyCode, Name, ModifiedDate )
VALUES  ( N'CYHh', -- CurrencyCode - nchar(3)
          'VGH', -- Name - Name
          '2012-03-25 19:45:40'  -- ModifiedDate - datetime
          )

If you try to run the code again, the constraint is violated and the row is not inserted.

6) In a new query window type

BULK INSERT AdventureWorks2008R2.Sales.Currency1 FROM 'c:\currency.dat'

to insert the rows from the text file

7) Now if you do another “SELECT * FROM SALES.CURRENCY1″, you will see that all the rows were inserted in the table and for some reason the constraint did not “work”.

Have a look at the picture below to see what I mean

Why is that happening? Most people do not know that and they are confused with the results.We have to alter the BULK INSERT statement.

8) Now we need to delete the contents of the table again by typing “DELETE Sales.Currency1″

9)  In a new query window type

BULK INSERT AdventureWorks2008R2.Sales.Currency1 FROM 'c:\currency.dat' WITH (CHECK_CONSTRAINTS)

to insert the rows from the text file.

In this case we will get an error (no data will be inserted into the table) because the constraint will be applied this time and since it is violated no rows will be inserted.

Always bear in mind that when you execute BULK INSERT statements in tables where there are constraints.You have to explicitly define it in the BULK INSERT statement for the constraints to be applied.

10) Now I am going to show you something similar by writing a trigger that will be applied to the same table.First we need to drop the constraint


USE [AdventureWorks2008R2]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Sales].[CK_Currency1]') AND parent_object_id = OBJECT_ID(N'[Sales].[Currency1]'))
ALTER TABLE [Sales].[Currency1] DROP CONSTRAINT [CK_Currency1]
GO

11) Now I am going to write the trigger. This trigger will make the contents of the Name column to uppercase when they are inserted in the table.Please keep in mind that we have no data in the table at this time.Execute the statement below


CREATE TRIGGER makeuppercase ON sales.Currency1
AFTER INSERT
AS
UPDATE sales.Currency1
SET Name= UPPER(Name)
WHERE CurrencyCode IN (
SELECT CurrencyCode FROM inserted
)
--now if we insert the first row in the table the trigger will fire
INSERT INTO sales.Currency1
( CurrencyCode, Name, ModifiedDate )
VALUES  ( 'dsg','dd',GETDATE()

)

-- You can check it out by a simple select
SELECT * FROM Sales.Currency1

--then delete this row
DELETE Sales.Currency1

12) Now we will insert the data from the text file using the BULK INSERT statement.

BULK INSERT AdventureWorks2008R2.Sales.Currency1 FROM 'c:\currency.dat'

13) Run a “Select * from Sales.Currency1″.Note that the trigger did not fire. Have a look at the picture below

14) Run a “DELETE Sales.Currency1″ statement to delete the rows in the table.

BULK INSERT AdventureWorks2008R2.Sales.Currency1 FROM 'c:\currency.dat' WITH (FIRE_TRIGGERS)

15) Run a “SELECT * FROM Sales.Currency1″. This time the trigger will fire and the contents of the Name column of the table will be in uppercase. Have a look at the picture below

Hope it helps!!!


Using the bcp utility to insert rows in a table with triggers and constraints March 26, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
3 comments

In this post I would like to talk about how to import and export data using the bcp utility. I will also explain how the bcp utility works with tables that have constraints and triggers attached on them. No databases exist in isolation. We need to import data from other databases. We need to import data from text files,spreadsheets.We also need to export data to other systems or databases.One way to achieve is using the bcp utility.

We can define bcp in the form of e.g  BCP table/query. We can define if we want to export data from a table or if we will export data using a query.We can specify if we are bringing data in the table (in) or out of the table (out) or specify (queryout), if we are going to write a select query to specify the data we want to export.Finally we need to specify the name of the file that we are importing the data from or exporting data to.

I will proceed with a hands on example.We will need a database to work with. I will use AdventureWorks2008R2. You can download it here .
We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.I will be using the Sales.Currency table. I will be using bcp to extract data from this table to a text file.Then I will create another table Sales.Currency1 in the same database and I will import the data from the text file using bcp again inside this new table.

1) In a new command window type (Start–>Run and type cmd)

bcp AdventureWorks2008R2.Sales.Currency out Currency.dat  -T -c

That will export all rows from the table in the file Currency.dat file.

2) Now I am going to create the new table


USE AdventureWorks2008R2 ;
GO
SELECT  *
INTO    AdventureWorks2008R2.Sales.Currency1
FROM    AdventureWorks2008R2.Sales.Currency
WHERE   1 = 2

3) Now we need in the same command window to type

bcp AdventureWorks2008R2.Sales.Currency1 in Currency.dat -T –c

and insert the data in the Currency1 table.You will see that the data is in the table by executing a simple “SELECT * FROM Sales.Currency1″

4) We will delete the data in the table by typing “DELETE Sales.Currency1″ in the query window.

Now we will alter the definition of the table by adding a constraint and then use the bcp command to insert the rows again in the Currency1 table.We will add a constraint that does not allow in the Currency1 table to enter values that have less than 5 characters in the Name field of the table.Type the following T-SQL statement in a new query window.


USE AdventureWorks2008R2 ;
GO
ALTER TABLE [Sales].[Currency1]  WITH NOCHECK ADD  CONSTRAINT [CK_Currency1] CHECK  ((len([name])>(4)))
GO

ALTER TABLE [Sales].[Currency1] CHECK CONSTRAINT [CK_Currency1]
GO

5) We can now try to insert (our table is empty) a new row that will violate the constraint.


USE AdventureWorks2008R2 ;
GO
INSERT INTO sales.Currency1
        ( CurrencyCode, Name, ModifiedDate )
VALUES  ( N'CYHh', -- CurrencyCode - nchar(3)
          'VGH', -- Name - Name
          '2012-03-25 19:45:40'  -- ModifiedDate - datetime
          )

If you try to run the code again, the constraint is violated and the row is not inserted.

6) In the command window type the bcp statement to insert the rows from the text file

bcp AdventureWorks2008R2.Sales.Currency1 in Currency.dat -T –c

7) Now if you do another “SELECT * FROM SALES.CURRENCY1″, you will see that all the rows were inserted in the table and for some reason the constraints did not “work”.

Have a look at the picture below to see what I mean

Why is that happening? Most people do not know that and they are confused with the results.We have to alter the bcp command.

8) Now we need to delete the contents of the table again by typing “DELETE Sales.Currency1″

9) In the same command window type bcp AdventureWorks2008R2.Sales.Currency1 in Currency.dat -T –c –h “CHECK_CONSTRAINTS”

Now if you press ENTER you will get an error (no data will be inserted into the table) because the constraint will be applied this time.

Have a look at the picture below to see that I mean


Always bear in mind that when you execute bcp commands in tables where there are constraints.You have to explicitly define it in the bcp statement for the constraints to be applied.

10) Now I am going to show you something similar by writing a trigger that will be applied to the same table.First we need to drop the constraint


USE [AdventureWorks2008R2]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Sales].[CK_Currency1]') AND parent_object_id = OBJECT_ID(N'[Sales].[Currency1]'))
ALTER TABLE [Sales].[Currency1] DROP CONSTRAINT [CK_Currency1]
GO

11) Now I am going to write the trigger. This trigger will make the contents of the Name column to uppercase when they are inserted in the table.Please keep in mind that we have no data in the table at this time.Execute the statement below


CREATE TRIGGER makeuppercase ON sales.Currency1
AFTER INSERT
AS
UPDATE sales.Currency1
SET Name= UPPER(Name)
WHERE CurrencyCode IN (
SELECT CurrencyCode FROM inserted
)
--now if we insert the first row in the table the trigger will fire
INSERT INTO sales.Currency1
( CurrencyCode, Name, ModifiedDate )
VALUES  ( 'dsg','dd',GETDATE()

)

-- You can check it out by a simple select
SELECT * FROM Sales.Currency1

--then delete this row
DELETE Sales.Currency1

12) In the command window type the bcp statement to insert the rows from the text file

bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T –c

13) Run a “Select * from Sales.Currency1″.Note that the trigger did not fire. Have a look at the picture below

14) Run a “DELETE Sales.Currency1″ statement to delete the rows in the table. Now run the bcp statement again in the command window

bcp AdventureWorks2008R2.Sales.Currency1 in Currency.dat -T –c -h “FIRE _TRIGGERS”

15) Run a “SELECT * FROM Sales.Currency1″. This time the trigger will fire and the contents of the Name column of the table will be in uppercase. Have a look at the picture below

Hope it helps!!!


Follow

Get every new post delivered to your Inbox.

Join 1,787 other followers