Bulk Insert to load, ...?... to unload

To load data from a file into a table, I use :

BULK INSERT Base.Prop.Table
FROM 'Path of file'
WITH (FieldTerminator='_', RowTerminator='_\n')

What's the command to unload a table to a file ?

Somebody must know... help... please...Originally posted by Karolyn
To load data from a file into a table, I use :

BULK INSERT Base.Prop.Table
FROM 'Path of file'
WITH (FieldTerminator='_', RowTerminator='_\n')

What's the command to unload a table to a file ?

Somebody must know... help... please...

y dont u just use a DTS package to do the load and unload?|||I don't want to use DTS packages because :

1) for the loading------------------
- the VB-SQLServer program is installed on lots of PCs*
- each day, these PCs download new files
- it's the name of the files that determines in witch table it must be loaded
- the structure of table can be changed anyday by distance by the program

So if I use DTS packages, I'll have to create or reconstruct the packages
each time the program wants to load datafiles.

2) for the unloading------------------
- each time the program is shut down, it unloads some data from tables
- the list of these tables can be changed anytime
- the data unloaded contains some calculated colums that depends that is different for each user

So if I use DTS packages, I'll have for each PCs users different packages and I don't want to have differences in each databases
(for maintenance ease).

*more than 3000 users in France|||What about bcp?|||I've got to put a special column delimiter
Can I detemine it with Bcp ?|||Originally posted by Karolyn
I've got to put a special column delimiter
Can I detemine it with Bcp ?
Use format file.|||Format file in the bcp command ?|||Originally posted by Karolyn
Format file in the bcp command ?

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f FORMAT_FILE :)] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]|||Mister 4 star Snail... I bow and thank you.|||Originally posted by Karolyn
Mister 4 star Snail... I bow and thank you.

For France I could do anything ;)

bulk insert question

Hi,
I'm getting file (from mainfraim) which has to be imported
into sql server 2000 empty table using bulk insert:
bulk insert database..table
from 'f:\data.dat'
with (
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
CODEPAGE = 'raw'
)
Problem is that file itself has 6GB, and data in the table
after bulk insert is around 10GB. Why is this difference?
Can it be because of the code page?
Thanks,
OJ
How are you discerning the size of the table? Are there non-clustered
indexes present?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ
|||For data size in the table, I'm using sp_spaceused (data
value). It returns accurate values because table is empty
before bulk insert. There aren't any indexes on the table.
>--Original Message--
>How are you discerning the size of the table? Are there
non-clustered
>indexes present?
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
imported[vbcol=seagreen]
table[vbcol=seagreen]
difference?
>
>.
>
|||Can you post DDL for the table? Also, how many rows of data are in the
table after the BULK INSERT?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx.gbl...[vbcol=seagreen]
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> non-clustered
> message
> imported
> table
> difference?
|||I suggest you read up on page layout, extents and also row layout in SQL Server. It is possible that not all
pages are full. Also, each row has some overhead as does each page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"OJ" <anonymous@.discussions.microsoft.com> wrote in message news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ
|||Hi OJ,
Have you run a dbcc showcontig on the table after the bulk insert (see BOL
for details)? I noticed you said that the table has no clustered indexes.
In my experience with bulk insert without a clustered index on the table it
can end up with a lot of unused space after the bulk insert. This might be
what is happening.
Wayne Antinore
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx.gbl...[vbcol=seagreen]
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> non-clustered
> message
> imported
> table
> difference?

bulk insert question

Hi,
I'm getting file (from mainfraim) which has to be imported
into sql server 2000 empty table using bulk insert:
bulk insert database..table
from 'f:\data.dat'
with (
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
CODEPAGE = 'raw'
)
Problem is that file itself has 6GB, and data in the table
after bulk insert is around 10GB. Why is this difference?
Can it be because of the code page?
Thanks,
OJHow are you discerning the size of the table? Are there non-clustered
indexes present?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf9201c45166$b423b6d0$a001280a@.phx
.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ|||For data size in the table, I'm using sp_spaceused (data
value). It returns accurate values because table is empty
before bulk insert. There aren't any indexes on the table.
>--Original Message--
>How are you discerning the size of the table? Are there
non-clustered
>indexes present?
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1bf9201c45166$b423b6d0$a001280a@.phx
.gbl...
imported[vbcol=seagreen]
table[vbcol=seagreen]
difference?[vbcol=seagreen]
>
>.
>|||Can you post DDL for the table? Also, how many rows of data are in the
table after the BULK INSERT?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx
.gbl...[vbcol=seagreen]
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> non-clustered
> message
> imported
> table
> difference?|||I suggest you read up on page layout, extents and also row layout in SQL Ser
ver. It is possible that not all
pages are full. Also, each row has some overhead as does each page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"OJ" <anonymous@.discussions.microsoft.com> wrote in message news:1bf9201c45166$b423b6d0$a001
280a@.phx.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ|||Hi OJ,
Have you run a dbcc showcontig on the table after the bulk insert (see BOL
for details)? I noticed you said that the table has no clustered indexes.
In my experience with bulk insert without a clustered index on the table it
can end up with a lot of unused space after the bulk insert. This might be
what is happening.
Wayne Antinore
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx
.gbl...[vbcol=seagreen]
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> non-clustered
> message
> imported
> table
> difference?