BULK INSERT with diff Data types

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

SQL Server Development (2000)

BULK INSERT with diff Data types

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2001-11-29 : 22:37:42

hi guys u are doing great job...
i am using BULK INSERT for inserting a huge file in to database..
but i am not clear can i specify the datatype in bcp.fmt file

current my bcp.fmt file

1 SQLCHAR 0 1 "" 1 record_type
2 SQLCHAR 0 6 "" 2 serial_number

can i use some thing like this

1 SQLCHAR 0 1 "" 1 record_type
2 SQLnumeric 0 6 "" 2 serial_number

i.e. use other datatype other than char.. if so how

bye

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2001-11-30 : 09:15:18

Khalik,

I've experimented with that but not had much luck. I usually import everything into a table that is all characters. Then I can use SQL to test for valid values using functions such as IsDate and IsNumeric. After than I transfer the data to my main table using INSERT-SELECT statements and lots of CONVERTS. Works wonderfully.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-30 : 09:36:44

But beware:


SELECT n, IsNumeric(n) FROM (
  SELECT '.' n
  UNION ALL SELECT ','
  UNION ALL SELECT '+'
  UNION ALL SELECT '-'
  UNION ALL SELECT '$'
  UNION ALL SELECT '£'
  UNION ALL SELECT '¥'
  UNION ALL SELECT '¤'
  UNION ALL SELECT '$+,.') a

Result:


n                
---- ----------- 
.    1
,    1
+    1
-    1
$    1
£    1
¥    1
¤    1
$+,. 1

Edited by - Arnold Fribble on 11/30/2001 09:44:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources