Can i order by on bulk insert? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Can i order by on bulk insert?

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-09-19 : 03:12:32

Hi i add a clustered index as advised.
My columns are REGNO,POLICYNO,PRODUCT,ISSUE_DATE
my csv file though have them as PRODUCT REGNO POLICYNO ISSUE_DATE
i bulk insert:


BULK INSERT [dbo].[dbfile]
FROM 'C:\databasebulks\totaltestfile.csv'
WITH (MAXERRORS = 500000,LASTROW = 100, --CODEPAGE ='ACP',  
CODEPAGE ='1253',
ORDER(REGNO,POLICYNO,PRODUCT,ISSUE_DATE),
FORMATFILE='C:\databasebulks\totaltestfile.xml'); 
--PRINT '.. Completed: Bulk Insert into [dbo].[dbfile].'

my xml:


<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm"  TERMINATOR = ";" MAX_LENGTH="100"  />
<FIELD ID="2" xsi:type="CharTerm"  TERMINATOR = ";" MAX_LENGTH="100"  />
<FIELD ID="3" xsi:type="CharTerm"  TERMINATOR = ";" MAX_LENGTH="100"  />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR = "\r\n" MAX_LENGTH="100"  />
</RECORD>
<ROW>
<COLUMN SOURCE="1" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="2" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="3" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="4" xsi:type="SQLNVARCHAR" />
</ROW>
</BCPFORMAT>

However it will just insert the first column of the csv to the 1st column of the database,the second column of the csv to the second column of the database etc.
Do i do something wrong, is this by design?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources