Using the bcp utility to insert rows in a table with triggers and constraints March 26, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: bcp, data export, data import
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)
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
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
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!!!