BULK INSERT mydb.myschema.myobject
from '\\UNC_path\myobjectdata.txt'
 WITH 
 (       FIELDTERMINATOR = '	'  
--	,CODEPAGE = 'raw'
       , FIRSTROW = 2
)         
go

     Jul 13  

Sometimes this is me

set nocount on 
-- is "Ad Hoc Distributed Queries" enabled ? if not, enable it
declare @SQLConfigOption varchar(256) 
Create table #tmpConfigOption (configName varchar(128), MinValue varchar(15), MaxValue varchar(15), ConfigValue varchar(15), RunValue varchar(15)) 
Set @SQLConfigOption = 'sp_configure @configname = ''Ad Hoc Distributed Queries''' 
insert into #tmpConfigOption 
exec (@SQLConfigOption)  
if exists (select * from #tmpConfigOption where configName = 'Ad Hoc Distributed Queries' and RunValue = '0' ) 
 begin 
  exec sp_configure @configname = 'Ad Hoc Distributed Queries',  @configvalue =  '1' ; 
  RECONFIGURE WITH OVERRIDE;  
  End 
go

/*
 * Now the test : http://www.sqlservercentral.com/Forums/Topic449625-338-1.aspx#bm449892
 */
 
 /*
   -- content of Import_OpenRowset.csv
field1,field2
1,a
2,b
3,c
9223372036,10Pos
92233720368,11Pos
922337203685,12Pos
9223372036854,13Pos
92233720368547,14Pos
922337203685477,15Pos
9223372036854775,16Pos
922337203685477580,TheMaxBigInt   
 */
SELECT cast(field1 as varchar(10)) as field1
, cast(field2 as varchar(15)) as field2 
INTO dbo.file1table
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=X:\;'
, 'SELECT * from X:\Test\Import_OpenRowset.csv')
/*
  -- result
	Msg 232, Level 16, State 2, Line 5
	Arithmetic overflow error for type varchar, value = 9223372036.000000.
	The statement has been terminated.
	Msg 208, Level 16, State 1, Line 2
*/
go
SELECT cast(field1 as bigint) as field1
, cast(field2 as varchar(15)) as field2 
INTO dbo.file1table2
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=X:\;'
, 'SELECT * from X:\Test\Import_OpenRowset.csv')
go

Select * from dbo.file1table;
/*
Invalid object name 'dbo.file1table'.
*/
go
Select * from dbo.file1table2;
/*
	field1               field2
	-------------------- ---------------
	1                    a
	2                    b
	3                    c
	9223372036           10Pos
	92233720368          11Pos
	922337203685         12Pos
	9223372036854        13Pos
	92233720368547       14Pos
	922337203685477      15Pos
	9223372036854774     16Pos
	922337203685477632   TheMaxBigInt
*/
go

IF OBJECT_ID('dbo.file1table','U') IS NOT NULL
        DROP TABLE dbo.file1table
GO
IF OBJECT_ID('dbo.file1table2','U') IS NOT NULL
        DROP TABLE dbo.file1table2
GO

/*
 * test as ended
 */
-- did we enable "Ad Hoc Distributed Queries" ? If yes, disable it 
if exists (select * from #tmpConfigOption where configName = 'Ad Hoc Distributed Queries' and RunValue = '0' ) 
  begin 
    exec sp_configure @configname = 'Ad Hoc Distributed Queries',  @configvalue =  '0' ; 
    RECONFIGURE WITH OVERRIDE;  
  End 
go

drop table #tmpConfigOption 
go

     Jul 13  

Sometimes this is me