Add identity in 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

General SQL Server Forums

New to SQL Server Programming

Add identity in bulk insert

Author

Topic

siprem
Starting Member

6 Posts

Posted - 2008-12-15 : 11:04:42

Hi All ,
Please tell me how to use identity while bulk inserting a file.

eg
Create table #tempfile (line varchar(8000),row int identity(1,1))
BULK INSERT #tempfile FROM '1.txt'

But this Bulk insert is throwing an error. Please help me

error message:
Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 11:18:24

as error suggests are you sure you specified correct row terminator. and just in case the data is not consistent specify format using format file

http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-16 : 04:27:32

Alternative method:

SET nocount ON

DECLARE @FileName varchar(8000)
DECLARE @NumLines int
DECLARE @XPCmdString varchar(8000)

SET @FileName = 'C:\temp\test.txt'
SET @XPCmdString = 'type ' + @FileName
CREATE TABLE #XPOutput (RowID int identity, XPLineOut varchar(1000))
INSERT INTO #XPOutput exec master.dbo.xp_cmdshell @XPCmdString

SELECT * FROM #XPOutput
Drop table #XPOutput

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources