拼成字串insert into ta(a,b,c) select 'a','3ab','tad','d3d3' union all select 'a','3ab','tad','d3d3' union all select 'a','3ab','tad','d3d3'.............
--> 类似这样的: if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (C1 varchar(11),C2 varchar(11),C3 varchar(11),C4 varchar(11)) insert into #T select 'a','3ab','tad','d3d3' union all select '3','dks','sie','d33d' union all select '3','3edff','csd','3ded'select C1,C2,C3,C4 from #T/* C1 C2 C3 C4 ----------- ----------- ----------- ----------- a 3ab tad d3d3 3 dks sie d33d 3 3edff csd 3ded */
a 3ab tad d3d3 3 dks sie d33d 3 3edff csd 3ded 没办法直接实现,再如何优化,也不如直接在数组里面循环调用SQL语句插入表,代码最简洁,效率最高
那就拼接 sql 吧insert into table1(a,b,c,e) select 'a','3ab','tad','d3d3' union select '3','dks','sie','d33d' ......
先用repalce ,代替空格 然后写成SQL导入不就可以吗?
这样的数据好象没什么好方法,要么拼insert语句,要么将数据编写成文件导入咯!
BULK INSERT test.dbo.[TB_1] FROM 'C:\Documents and Settings\Administrator\桌面\123.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , TABLOCK )
select 'a','3ab','tad','d3d3' union all
select 'a','3ab','tad','d3d3' union all
select 'a','3ab','tad','d3d3'.............
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (C1 varchar(11),C2 varchar(11),C3 varchar(11),C4 varchar(11))
insert into #T
select 'a','3ab','tad','d3d3' union all
select '3','dks','sie','d33d' union all
select '3','3edff','csd','3ded'select C1,C2,C3,C4 from #T/*
C1 C2 C3 C4
----------- ----------- ----------- -----------
a 3ab tad d3d3
3 dks sie d33d
3 3edff csd 3ded
*/
3 dks sie d33d
3 3edff csd 3ded 没办法直接实现,再如何优化,也不如直接在数组里面循环调用SQL语句插入表,代码最简洁,效率最高
select 'a','3ab','tad','d3d3'
union
select '3','dks','sie','d33d'
......
FROM 'C:\Documents and Settings\Administrator\桌面\123.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' ,
TABLOCK )
http://blog.csdn.net/dutguoyi/archive/2007/11/11/1879416.aspx