SELECT DISTINCT
t_bd_item_info.item_subno as item_subno,
t_bd_item_info.item_name as item_name,
t_bd_item_info.item_size as item_size,
t_bd_item_info.unit_no as unit_no,
t_im_branch_stock.stock_qty as stock_qty,
t_pc_branch_price.sale_price as sale_price,
t_bd_item_info.price as price
FROM t_im_branch_stock,
t_bd_item_info,
t_bd_item_cls,
t_bd_place_item_info,
t_pc_branch_price
WHERE (( t_im_branch_stock.item_no = t_bd_item_info.item_no ) and
( substring(t_im_branch_stock.branch_no,1,2) = t_pc_branch_price.branch_no ) and
( t_pc_branch_price.item_no = t_im_branch_stock.item_no ) and
( t_bd_item_info.item_clsno *=t_bd_item_cls.item_clsno ) and
( t_im_branch_stock.item_no *= t_bd_place_item_info.item_no ) and
( t_im_branch_stock.branch_no *= t_bd_place_item_info.branch_no ) and
( t_bd_item_cls.item_flag = '0' ) ) and
( t_im_branch_stock.branch_no like '%%' ) and
( t_bd_item_info.status <> '2') and
t_im_branch_stock.item_no like '%' and
t_bd_item_info.item_clsno like '%' and
t_bd_item_info.item_subno like '%' and
t_pc_branch_price.supcust_no like '%' and
t_bd_place_item_info.place_no like '%' and
t_bd_item_info.item_brand like '%' and
t_bd_item_info.item_name like '%%'
怎样把上面的查询结果直接通过BCP的方式导成“shopinfo.txt”?
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:\dt.txt -c -S“服务器” -U“sa” -P“密码”'
--找出自己想要的行数据:要加QUERYOUT OR QUERYOUTIN
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.成绩" queryout d:\test\123.xls -c -S"PC2011061511NRV" -U"sa" -P"30687724"'————————————————————————————————————————--从文件中导入数据到数据库对应表中,-U用IP或用户名都可以。”-q”要不要都行,-q是若要指定包含空格或单引号的数据库名称,必须使用 -q 选项:
EXEC master..xp_cmdshell 'bcp test.dbo.成绩 in d:\test\123.xls -c –q -S"127.0.0.1" -U"sa" -P"30687724"'注:以上方法对于XLSX(好像打不开,没有懂)、CSV(要分裂这个还没有搞明白)文件也是支持的。一般用TXT,小文件用XLS即可。复制50W也就5秒,快死了。如果是TXT文件,最好格式要求为:“文本文件(制表符分隔)”而且原表的数据类型要求为“VARCHAR(50)”
EXEC master..xp_cmdshell 'bcp test.dbo.book1 in D:\test\yh_2010-03-2\2010-3-2\2010-3-2.txt -t"\t" -c -S"127.0.0.1" -U"sa" -P"30687724"'