some MSSQL examples
--date=2019-12-05
--group="笔记草稿"
---------
分页
1 /* >= 2012 2 跳过 5 条, 取出 10 条 3 --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15 4 */ 5 SELECT id FROM Table_test 6 Order by id Offset 5 Rows Fetch Next 10 Rows Only 7 8 9 /* >= 2005 10 跳过 3 条, 取出 5 条 11 --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15 12 */ 13 SELECT TOP 5 14 * 15 FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS rownumber , 16 * 17 FROM Table_test 18 WHERE someCol > someVal 19 ) A 20 WHERE rownumber > 3
位运算 和 Case When
1 /* 2 --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15 3 --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/bitwise-operators-transact-sql?view=sql-server-ver15 4 */ 5 6 /* 与, 或 */ 7 UPDATE tab_test 8 SET some_int = CASE WHEN (some_int IS NULL) then 1 9 ELSE (some_int | 1) END 10 WHERE (some_int IS NULL) OR (some_int & 1) = 0 11 12 /* 异或, 自己 异或 效果就是把值设置为0 */ 13 UPDATE tab_test 14 SET some_int = (some_int ^ some_int) 15 WHERE some_int IS NOT NULL 16 17 /* 按位取反 */ 18 UPDATE tab_test 19 SET some_int = (~some_int) 20 WHERE some_int IS NOT NULL
数据导入和导出
--see-also=https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-server-ver15
BCP
/* --see-also=https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-ver15 */ /* 导出 格式化文件 非XML */ bcp [testDb].[dbo].[table_test] format nul -f bcpFmtTest.fmt -n -T /* 导出 格式化文件 XML --see-also=https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/xml-format-files-sql-server?view=sql-server-ver15 */ bcp [testDb].[dbo].[table_test] format nul -x -f bcpFmtTest.xml -c -T /* 导出 数据 */ bcp [testDb].[dbo].[table_test] out exportData.bcp -f bcpFmtTest.fmt -T /* 只导出特定的列 */ bcp "select col1, col2 from [testDb].[dbo].[table_test]" queryout exportData.bcp -f bcpFmtTest.fmt -T /* 导入 */ bcp [testDb].[dbo].[table_test] in testData.bcp -f bcpFmtTest.fmt -T
非XML格式化文件 例
--see-also=https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/use-a-format-file-to-skip-a-table-column-sql-server?view=sql-server-ver15
/* 跳过表列: fmt文件内容 导入例 12.0 3 1 SQLCHAR 0 255 "," 2 some_text Chinese_PRC_CI_AS 2 SQLINT 0 4 "," 3 some_int "" 3 SQLINT 0 4 "\r\n" 4 another_int "" */
XML格式化文件
/* 跳过表列: xml文件内容 例 --see-also=同上
在 BULK INSERT 中, 貌似 xml 文件的 COLUMN 的 name 顺序需要与表相同, 而 source 所指定的 FIELD id 为使导入的数据正确可以任意顺序 引用: 使用 XML 格式化文件时,你无法在通过 bcp 命令或 BULK INSERT 语句直接向表导入内容时跳过列。
但是,您可以向表中除最后一列的所有列导入。
如果必须跳过最后一列以外的任何列,必须创建仅包含数据文件所含列的目标表视图。
然后,您可以将此文件中的数据大容量导入此视图。 <?xml version="1.0"?> <BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="255" COLLATION="Chinese_PRC_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="id" xsi:type="SQLINT"/> <COLUMN SOURCE="2" NAME="some_text" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT> */
BULK INSERT 导入
/* --see-also=https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver15 */ BULK INSERT table_test FROM 'E:\bcpTest.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); BULK INSERT table_test FROM 'E:\bcpTest.txt' WITH ( formatFile='E:\bcpFmtTest.xml' );
OPENROWSET(BULK ...) 导入
/* --see-also=同上 */ INSERT INTO table_test (id, some_text) SELECT id, some_text FROM openRowSet(BULK 'E:\bcpData.txt', formatFile='E:\bcpFmtTest.xml' ) as t1 ;
--------- THE END ---------