[dba-SQLServer] [AccessD] Bulk copy spec - was RE: using a saved SSIS with VB.Net

Eric Barro ebarro at verizon.net
Tue May 1 09:06:36 CDT 2007


John,

This is the SQL query I use to get the field names...

DECLARE @TableName	varchar(50)

SET @TableName = 'tmpemployees'

SELECT syscolumns.name AS DBFieldNames,
syscolumns.type AS DataType,
(syscolumns.length/2) AS DataLength
FROM sysobjects 
INNER JOIN syscolumns 
	ON sysobjects.id = syscolumns.id 
WHERE sysobjects.name = @TableName 
ORDER BY colorder 

Here's what I came across when I googled it...

http://www.thescripts.com/forum/thread520822.html

8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""

The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.

First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.

The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.

Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.

Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.



Eric



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, May 01, 2007 4:06 AM
To: 'Access Developers discussion and problem solving'
Cc: dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Bulk copy spec - was RE: using a saved SSIS with VB.Net

Is there any way to have SQL Server export a spec for a table that bulk copy
can use, at least what the fields look like etc?  I have never used bulk
copy and there are a LOT of fields in the table.  Alternately is there
somewhere that I can find the bulk copy spec, what the format file is
supposed to look like?


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Monday, April 30, 2007 7:31 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net

Have you looked into BULK INSERT in SQL? This is supposed to be a faster
data import method.


Using T-SQL you can do something like this...

CREATE TABLE #tmpEmployees (<fieldnames here>) BULK INSERT #tmpEmployees
FROM 'c:\temp\import.csv'
WITH (FORMATFILE = 'c:\temp\importCSV.fmt'

importCSV.fmt would contain the file format...in this example it's fixed
width

8.0
18
1   SQLCHAR   0   5   ""     1	suffix	SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0   30   ""   2	last_name	SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0   20   ""   3	first_name	SQL_Latin1_General_CP1_CI_AS


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.2/782 - Release Date: 5/1/2007 2:10
AM
 




More information about the dba-SQLServer mailing list