help,a text how to sql table code ?

i have a text as follow, line with ¡°|¡±and {LF},

8|-000000186075919.|+000000000387820.|2008-03-31|20010423|
9|-000000000003919.|-000000000123620.|2008-03-31|20010123|
8|-000000018623419.|+000000000381230.|2008-05-30|20010423|




i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandate
smalldatetime£¬update smalldatetime£¬after to sql table is as follow,


sign year month trandate update
8 -186075919.000 387820.000 3/31/2008 4/23/2001
9 -3919.000 -123620.000 3/31/2008 1/1/2001
8 -18623419.000 387820.000 5/30/2008 4/23/2001



could you help me how write the sql code ?

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote:

If the text is in a file, you can use bcp to import them into a table with
the desired columns. The bcp.exe command line would be similar to the
following (assuming that junk is the table name and junk.txt is the file
containing the text):

cmd>bcp myDB..junk in junk.txt -c -SmyServer -T -t "|"

If the text is already inside a single column in a table, you can use T-SQL
substring(), charindex(), and so on to parse the strings. But that's ugly.
I'd still would just bcp the column out to the file, and bcp the file back
into the table with multiple colunms.

Linchi

"agiler" wrote: