显示标签为“datafile”的博文。显示所有博文
显示标签为“datafile”的博文。显示所有博文

2012年3月8日星期四

BCP Format File

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?Rathtap (amcniw@.yahoo.com) writes:
> A format file provides a way to bulk copy data selectively from a data
> file to an instance of SQL Server. This allows the transfer of data to
> a table when there is a mismatch between fields in the data file and
> columns in the table.
> I take it this assumes the number of fields in the data file will
> always be constant. What if it is not?
> My table has two columns but my datafile may have 2 to 4 columns and I
> want to always select only the first two. Is there a way to set up the
> format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the table:
CREATE TABLE [dbo].[CT_RATE_TEMP] (
[CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODERATE] [smallmoney] NULL ,
[CODERATE2] [smallmoney] NULL ,
[CODERATE3] [smallmoney] NULL ,
[CODERATE4] [smallmoney] NULL
) ON [PRIMARY]
GO

And here is the import file:
3641525.00
5275924.50
5276012.77
5277524.03
7428516.88
744367.84
800037.58
800048.00
800058.92
800068.94
800079.32
800089.66
800099.90
800109.90
8001110.08
8001210.29
Here I would like Col1 of the file to map to the Code field and Col2
to CodeRate. CODERATE2,CODERATE3 and CODERATE4 should be null.

On the other hand there may be the following file to import where all
fields match, then there is no problem:
5275924.5024.5024.50
5276012.7712.7712.77
5277524.0324.0324.03
7428516.8816.8816.88
744367.847.847.84
800037.587.587.58
800048.008.008.00
800058.928.928.92
800068.948.948.94
800079.329.329.32
800089.669.669.66
800099.909.909.90
800109.909.909.90
8001110.0810.0810.08
8001210.2910.2910.29
8001612.0512.0512.05
8001812.1412.1412.14
8001912.6112.6112.61
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AEDF7A33AYazorman@.127.0.0.1>...
> Rathtap (amcniw@.yahoo.com) writes:
> > A format file provides a way to bulk copy data selectively from a data
> > file to an instance of SQL Server. This allows the transfer of data to
> > a table when there is a mismatch between fields in the data file and
> > columns in the table.
> > I take it this assumes the number of fields in the data file will
> > always be constant. What if it is not?
> > My table has two columns but my datafile may have 2 to 4 columns and I
> > want to always select only the first two. Is there a way to set up the
> > format file to accomplish that?
> Don't know off hand. Can you post a sample of the input file, and a CREATE
> TABLE statement for your table?|||try adding default values to the null fields

BCP format file

Has anyone successfully used a BCP format file to import from a data
file with fewer fields than there are columns in the table you're
importing into? I've been reading the Books Online help and trying to
manipulate this format file for hours and am getting nowhere. BCP tells
me "Starting copy..." but then "0 rows copied" and for the life of me I
can't figure out why. Is there any additional documentation (or war
stories!) anywhere?Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Has anyone successfully used a BCP format file to import from a data
> file with fewer fields than there are columns in the table you're
> importing into? I've been reading the Books Online help and trying to
> manipulate this format file for hours and am getting nowhere. BCP tells
> me "Starting copy..." but then "0 rows copied" and for the life of me I
> can't figure out why. Is there any additional documentation (or war
> stories!) anywhere?
The format file has a couple of columns that describes the fields in the
text file.
Column 1 - Field number in file, normally they run from 1 to the number
you have on line2 in the file.
Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
Column 3 - Prefix length. Only used with binary files.
Column 4 - Field length. Used when you have fixed-length fields.
Column 5 - Field terminator. Note that the row terminator is really
only the field terminator for the last field.
Column 6 - Column number in the table, starting on 1. 0 means "don't
import this field". Thus importing only some columns is not very
difficult.
Column 7 - Normally used for the column name, but BCP does not look at
this data. It's the column number that counts.
Column 8 - Collation, can be set to "", unless you want some conversion.
Important to understand is that BCP always reads the file as if it was
binary. BCP reads bytes for field 1, until it finds the end of that
column, by prefix length, fixed length or terminator. Then it goes on
to field2 etc, until it starts over. For BCP newline has no particular
significance, so if it gets out of sync for some reason, there is
way for it to resync.
If the above does not help you, please post:
o CREATE TABLE statement for your table.
o The format file.
o A sample data file, preferrably in attachment to avoid damage
in news transport.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I just imported some rows with a format file, but am having
difficulty importing a date field correctly from my data file, which is
four fields delimited by tabs:
FX 2006-05-04 10:10 1103SWLSO1 XYZ
In the table I'm importing into, these columns are all CHAR except the
second which is DATETIME.
In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
Yet in the table that second field gets read in as:
0639-03-09 702:16:41.537
Help would be much appreciated.
In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The format file has a couple of columns that describes the fields in the
> text file.
> Column 1 - Field number in file, normally they run from 1 to the number
> you have on line2 in the file.
> Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
> Column 3 - Prefix length. Only used with binary files.
> Column 4 - Field length. Used when you have fixed-length fields.
> Column 5 - Field terminator. Note that the row terminator is really
> only the field terminator for the last field.
> Column 6 - Column number in the table, starting on 1. 0 means "don't
> import this field". Thus importing only some columns is not very
> difficult.
> Column 7 - Normally used for the column name, but BCP does not look at
> this data. It's the column number that counts.
> Column 8 - Collation, can be set to "", unless you want some conversion.
> Important to understand is that BCP always reads the file as if it was
> binary. BCP reads bytes for field 1, until it finds the end of that
> column, by prefix length, fixed length or terminator. Then it goes on
> to field2 etc, until it starts over. For BCP newline has no particular
> significance, so if it gets out of sync for some reason, there is
> way for it to resync.
> If the above does not help you, please post:
> o CREATE TABLE statement for your table.
> o The format file.
> o A sample data file, preferrably in attachment to avoid damage
> in news transport.
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks. I just imported some rows with a format file, but am having
> difficulty importing a date field correctly from my data file, which is
> four fields delimited by tabs:
> FX 2006-05-04 10:10 1103SWLSO1 XYZ
> In the table I'm importing into, these columns are all CHAR except the
> second which is DATETIME.
> In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
> Yet in the table that second field gets read in as:
> 0639-03-09 702:16:41.537
I said this in my previous post:
I should have clarified: the data type in the *file*. Anything else
than SQLCHAR or SQLNCHAR means that you have a binary file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Rick,
If you have SQLDATETIME and length 16 in your format file, then
you have told bcp to expect binary data, not text. This is also suggested
by the fact that 702:16:41.537 is the time that could be represented by
misconverted character data. That specific time would be displayed if
a datetime held the 0x2D3531A5 as its last 4 bytes, and this is the
ASCII representation of '-51' More likely values to come from date
strings are similar. If the ascii representation of '2006-05-04' were read
into a datetime, the time portion would correspond to 701:58:33:483.
Or in short, as Erland said, change your format file to say SQLCHAR
(or SQLNCHAR, as appropriate) instead of SQLDATETIME.
Steve Kass
Drew University
Rick Charnes wrote:
>Thanks. I just imported some rows with a format file, but am having
>difficulty importing a date field correctly from my data file, which is
>four fields delimited by tabs:
>FX 2006-05-04 10:10 1103SWLSO1 XYZ
>In the table I'm importing into, these columns are all CHAR except the
>second which is DATETIME.
>In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
>Yet in the table that second field gets read in as:
>0639-03-09 702:16:41.537
>Help would be much appreciated.
>In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
>says...
>

2012年2月13日星期一

Batch File.

I am creating one batch file which move datafile and logfile into different
machine but I want to ask one question like if one command successfully
executed then I have to wait for 20 seconds and then execute the second
command .... can any one know the command which I can use in batch file...
I think there should be any wait or delay sort of command available....
Thanks in advance.
Joh wrote:
> I am creating one batch file which move datafile and logfile into
> different machine but I want to ask one question like if one command
> successfully executed then I have to wait for 20 seconds and then
> execute the second command .... can any one know the command which I
> can use in batch file... I think there should be any wait or delay
> sort of command available....
>
> Thanks in advance.
If you kick off the batch file from a stored procedure or SQL batch
file, you can use the WAITFOR DELAY command to wait for 20 seconds.
David Gugick
Imceda Software
www.imceda.com
|||I have created the Batch File in which I wrote this...
NET STOP SQLSERVERAGENT
NET STOP MsSqlserver
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdbdata.mdf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdblog.ldf"
C:\Backup\SystemDatabase
NET START SQLSERVERAGENT
NET START MSSQLSERVER
Now I want to wait after first copy so what I have to write after that?
Thanks in advance.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#spQKiNVFHA.1200@.TK2MSFTNGP14.phx.gbl...
> Joh wrote:
> If you kick off the batch file from a stored procedure or SQL batch
> file, you can use the WAITFOR DELAY command to wait for 20 seconds.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> I have created the Batch File in which I wrote this...
> NET STOP SQLSERVERAGENT
> NET STOP MsSqlserver
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdbdata.mdf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdblog.ldf"
> C:\Backup\SystemDatabase
> NET START SQLSERVERAGENT
> NET START MSSQLSERVER
> Now I want to wait after first copy so what I have to write after
> that?
>
This is not a DOS newsgroup, and what I remember from those days is
somewhat limited. My suggestion was if you were using a stored procedure
to kick off the batch files, you could delay them using the waitfor
command. You would need to use xp_cmdshell to kick off the first batch
file, wait, then kick off the second, all from a stored procedure.
Why do you need to wait after the first copy?
David Gugick
Imceda Software
www.imceda.com
|||How can you start the SQL Service through SQL Stored procedure and through
XP_CMDSHELL .... ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uI5jN6WVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Joh wrote:
> This is not a DOS newsgroup, and what I remember from those days is
> somewhat limited. My suggestion was if you were using a stored procedure
> to kick off the batch files, you could delay them using the waitfor
> command. You would need to use xp_cmdshell to kick off the first batch
> file, wait, then kick off the second, all from a stored procedure.
> Why do you need to wait after the first copy?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> How can you start the SQL Service through SQL Stored procedure and
> through XP_CMDSHELL .... ?
>
You can't. I only suggested it before I actually knew what you were
doing. You need to start SQL Server outside a stored procedure, for
obvious reasons.
David Gugick
Imceda Software
www.imceda.com