SqlServer数据库相关积累

--数据库生成脚本容量过大,通过以下语句运行即可。

sqlcmd -S localhost -U sa  -P 123456 -d xk -i G:15_软件名称IRC_DB_Maker.sql

--查询某库中所有表名以及数量
SELECT   a.name AS 数据库名, MAX(b.rows) AS 记录条数
FROM      sys.sysobjects AS a INNER JOIN
                sys.sysindexes AS b ON a.id = b.id
WHERE   (a.xtype = 'u')
GROUP BY a.name
ORDER BY 记录条数 DESC

--前

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--本地Access数据导入本地sql(只导入数据)
insert  INTO qkmx_sum 
Select *
FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 'Data Source="G:a发石家庄期刊总表.mdb";User ID=Admin;Password=' )...qkmx总表        

---后

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 

--本地access导入本地sql

--在SQL SERVER 里运行:   zz  远程表名  count1  目标表名
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

SELECT *
INTO qkmx_sum
FROM OPENDATASOURCE ('Microsoft.Ace.OLEDB.12.0', 
'Data Source="G:a发石家庄期刊总表.mdb";User ID=Admin;Password=' )...qkmx总表
      
      
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure