HELP BULK Insert - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

HELP BULK Insert

Author

Topic

JaseDytec
Starting Member

14 Posts

Posted - 2008-08-11 : 11:21:11

Hi, i am using a bulk insert query to add .csv data to my table.

My CSV file contains 3 columns; Device, Duration, Units

But the SQL Table i want to insert this data into, has 5 columns, Device ID, Vehicle ID, Device, Duration, Units

Using a standard bulk insert query will not work

-- Select Database

USE [Database]

GO

--Add data
BULK 
INSERT Table
        FROM 'C:\File.csv'
            WITH
    (
	        FIRSTROW = 2,
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
								
    )
			 
GO

I would like SQLServer to automatically put in a Device ID and a set value for Vehicle ID (e.g. =2). How would i go about this, i have tried using bcp to create a format file but i havent had any success of getting it to work.

Thanks for your help

Jase

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:29:02

Make use of a format file.
Read about them in Books Online how to create them manually.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:33:10

[code]9.0
4
1 SQLCHAR 0 7 "," 1 PersonID ""
2 SQLCHAR 0 100 "," 0 ExtraField SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 3 LastName SQL_Latin1_General_CP1_CI_AS[/code]You set the column ordinal (left to table columnname) to zero, if you do not want to import that column from source.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 11:41:32

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

JaseDytec
Starting Member

14 Posts

Posted - 2008-08-12 : 11:53:02

Cheers for the help guys, think i may have to try and fix the bcp error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources