some MSSQL examples

    --date=2019-12-05

    --group="笔记草稿"

---------

 

分页

/* >= 2012 
   跳过 5 条, 取出 10 条
   --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15
 */
SELECT id FROM Table_test 
Order by id Offset 5 Rows Fetch Next 10 Rows Only


/* >= 2005 
    跳过 3 条, 取出 5 条
   --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15
 */
SELECT TOP 5
        *
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS rownumber ,
                    *
          FROM Table_test
          WHERE someCol > someVal
        ) A
WHERE   rownumber > 3

 

 

位运算 和 Case When

/*
   --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15
   --see-also=https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/bitwise-operators-transact-sql?view=sql-server-ver15
 */

/* 与, 或 */
UPDATE tab_test 
SET some_int = CASE WHEN (some_int IS NULL) then 1 
                    ELSE (some_int | 1) END
WHERE (some_int IS NULL) OR (some_int & 1) = 0

/* 异或, 自己 异或 效果就是把值设置为0 */
UPDATE tab_test 
SET some_int = (some_int ^ some_int)
WHERE some_int IS NOT NULL

/* 按位取反 */
UPDATE tab_test 
SET some_int = (~some_int)
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 ---------