use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([Col] nvarchar(100))
Insert #T
select N'123sdf980werwer498798810290fwer090asdas090900aaadsdas' union all
select N'456asdasadasdasd79879a8s7d9as7d9879s879a7sd98a7s9d7as' union all
select N'789dsda79sd79a8s7d9a8s7d9a8s7d9a8s7d9a8s7d98as79d87a9'
Go
Select 
[Col1]=LEFT(Col,3),
Col2=SUBSTRING(Col,24,4) ,
Col3=SUBSTRING(Col,40,6)
from #T

/*
Col1    Col2    Col3
123    0290    090900
456    s7d9    a7sd98
789    d9a8    s7d98a
*/
--txt讀取
select 
[Col1]=LEFT(Col,3),
Col2=SUBSTRING(Col,24,4),
Col3=SUBSTRING(Col,40,6) 
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=E:\;','select * from roy.txt')

------解决方案--------------------

SQL code
示例:
G:\data.txt文件内容如下:
AAAAAABBBBBCCCDD
1225415612324512
4564q21a3sdfdafe
545f121a7t9d5f65

创建一个表:
CREATE TABLE tb(col1 VARCHAR(6),col2 VARCHAR(5),col3 VARCHAR(3),col4 VARCHAR(4));

生成xml格式化文件
!!bcp MyTest.dbo.tb format nul -f G:\tb_fmt.xml -c -x -Smyfend\liangck -T

G:\tb_fmt.xml格式化文件内容如下(要适当修改):
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharFixed" LENGTH="6" COLLATION="Chinese_PRC_90_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="5" COLLATION="Chinese_PRC_90_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="3" COLLATION="Chinese_PRC_90_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="2" COLLATION="Chinese_PRC_90_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="col1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="col2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="col3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="col4" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

使用BULK INSERT导入数据
BULK INSERT tb
    FROM 'G:\data.txt'
WITH
(
    FORMATFILE='G:\tb_fmt.xml'
);


查看数据:

SELECT * FROM tb;

/*
col1   col2  col3 col4
------ ----- ---- ----
AAAAAA BBBBB CCC  DD
122541 56123 245  12
4564q2 1a3sd fda  fe
545f12 1a7t9 d5f  65

(4 行受影响)
*/