Bulk Insert With Conditions

_{Friday, January 30, 2015 8:39 PM}

I'm trying to bulk insert data into a table using the following script and I get a Primary Key Constraint Error... something like " violation of primary key constraint cannot insert duplicate values".  I can get around this by truncating the table and running the bulk insert again but the issue with doing that is I loose all the previous data.  All I'm trying to do is get a script that will allow me to bulk insert data into my table without getting the above error.  The purpose of the bulk insert is to replace pricing in a table with new pricing that changes monthly.  Currently I have to do it manually from the software that writes to the sql tables.  Please help!!!

BULK
insert dbo.test
FROM 'c:\test.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

_{Friday, January 30, 2015 10:03 PM ✅Answered}

First create TEST_STG same structure as of TEST TABLE.

Truncate and Load the data into TEST_STG table using bulk insert

BULK
insert dbo.emp_STG
FROM 'c:\empimports.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Then run the below merge statement

MERGE TEST T
USING TEST_STG TS 
ON T.PK_COLUMN= TS.PK_COLUMN
WHEN MATCHED THEN
  UPDATE
  SET T.Col1=TS.col1,T.col2=TS.col2
WHEN NOT MATCHED BY TARGET THEN
  INSERT (Col1,col2,col3)
  VALUES (TS.Col1,TS.col2,TS.col3);
 

--Prashanth

_{Thursday, February 5, 2015 11:19 PM ✅Answered}

You could do this from SQLCMD, which is a command line tool. Put the query in a file and run:

SQLCMD -S server -i "yourquery.sql" -o "output.txt"

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Monday, February 9, 2015 11:06 PM ✅Answered}

So you want to display the rows in the staging table that did not hit any rows?

You would have to do something like:

SELECT ...
FROM   dbo.stgitem
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.item
                   WHERE  item.code = stgitem.code)

You cannot easily to it in the MERGE statement, unless you really want to delete rows from the target table.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Friday, January 30, 2015 9:05 PM}

Hi there,

It seems you need to add formatfile in your script as well. I have not tried it.

please refer to the link below:

https://msdn.microsoft.com/en-us/library/ms186335.aspx

Good Luck

_{Friday, January 30, 2015 9:11 PM}

I would recommend to load the data into TEST_STG(truncate and load the data into Staging table) and then validate with test table. Either you can remove the duplicates Or you can use MERGE statement

Please refer the below link

https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

--Prashanth

_{Friday, January 30, 2015 9:18 PM}

***Prashanth***

Im a SQL newbie can you give me an example script to use?  Where would the "IF MATCHED" go in relation to the below scripts

BULK
insert dbo.emp
FROM 'c:\empimports.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

_{Tuesday, February 3, 2015 1:30 PM}

Prashanth,

What does the TS represent in the above script?

TEST_STG TS

TS.PL_COLUMN

ECT....

Shake-N-Bake2000

_{Tuesday, February 3, 2015 11:16 PM | 1 vote}

TS is a table alias. Without the alias, you would say TEST_STG.PL_COLUMN.

Using table aliases in your query is very much best practice. If you keep repeating table names all over the query, you quick hide the forest behind all those trees.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, February 4, 2015 1:21 PM}

Thanks Erland.  What does PL represent when you write PL_COLUMN and why do you use and underscore?  Do I need to use Underscores in scripts?

Shake-N-Bake2000

_{Wednesday, February 4, 2015 3:57 PM}

In this context the underscore is just another character like A, M or Z. That is, PL_COLUMN is just a name like Franz, Piero or Vladimir.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, February 5, 2015 8:43 PM}

Thanks Prashanth it worked!

One last question here.  Is there a way to kick of a sql.bak that would send my querry results to folder on my computer in .txt format?  When I run the above querry I the bellow text in the results pane of SQL.  I want to be able to have that automatically sent to a folder on my computer as a text file but using a script and not the management studio GUI. 

(120 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(120 row(s) affected)

Shake-N-Bake2000

_{Monday, February 9, 2015 2:10 PM}

Hi Erland

SQLCMD -S server -i "yourquery.sql" -o "output.txt" worked perfect thank you!

Shake-N-Bake2000

_{Monday, February 9, 2015 2:24 PM}

Now I'm trying to get only the items that were not updated to show in the results window, any idea how to do that?  For example when I run the below script I get the below which is what I want however I also want to see the non affected row in the results window is this possible?  What's happening is I'm bulk inserting and updating 120 records which contain price updates with a WHEN MATCHED statement so I want to see the NON MATCHED items in the sql results window when I run my script so I do not have to go through all 120 items manually to see which item did not update.  Do I do a WHEN NOT MATCHED BY SOURCE  THEN DELETE statement? and if so what would I write?  The row Im trying to return is "stgitem.avgunitcost" but only the item that didn't update of course.  Im using SQL Management Studio 2008

use test
truncate table dbo.stgitem

BULK
insert dbo.stgitem
FROM 'C:\PriceUpdate\priceupdate.csv'
WITH
(
KEEPIDENTITY,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

use test
merge dbo.item
using dbo.stgitem
on item.code = stgitem.code
when matched then
update
set item.avgunitcost = stgitem.avgunitcost,item.issuecost = stgitem.issuecost;

(120 row (s) affected)

(1 row (s) affected)

(1 row (s) affected)

(119 row (s) affected)

Shake-N-Bake2000