I am having problems with Bulk Copy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I am having problems with Bulk Copy

Author

Topic

Cwm
Starting Member

39 Posts

Posted - 2008-12-02 : 09:18:58

Hi,
I am having an issue with the bulk copy command. I have a csv file that I am using the bulk copy on. In the table i have an autoincrementing id. When i run the bulk copy it misses the first column of data. But if i add the comma delimiter to the beginning of each line in the csv file, then it gets the first data column.

So how do i use the bulk copy command to get all the columns in the csv file without having to put a delimiter to the beginning of each line prior to using the bulk copy?

Thanks
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 09:50:16

Bulk Copy? Are you using SSIS?
Do you mean BULK INSERT?

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-02 : 10:17:40

Create a View which selects all the columns of the table to be inserted except for the identity (incrementing) column. Then do bulk insert on the View, e.g.
BULK INSERT
[dbo].[vw_TableName]
FROM 'C:\temp\c.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS,
MAXERRORS = 0
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:27:50

Why not simply use the

KEEPIDENTITY

switch?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:29:31

Works with BCP too (the -E switch) and even OPENROWSET!

INSERT	TargetTable
WITH	(
		KEEPIDENTITY
	)
	(
		DepartmentID,
		Name,
		GroupName,
		ModifiedDate
	)
SELECT	*
FROM	OPENROWSET(BULK '\\pc1\data.txt') AS src

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-02 : 11:37:42

The Keepidentity allows the identity column to be written to and will only work if the csv file has a column to match the identity column.
So it could cause problems if not maintained properly.
By ignoring the identity column by using a View you can leave SQL Server to handle auto-increment.

Cwm
Starting Member

39 Posts

Posted - 2008-12-02 : 19:15:55

Thanks for the suggestions guys! I will try out your suggestions tonight at work.
Peso, I was using the Bulk insert, i had made a mistake.

Thanks everyone :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources