# sqlserver

TIP

整理常用的 Sql 语法使用。

# Upate

1.根据表关联的查询结果进行批量更新数据

UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

2.根据with cte的查询结果进行批量更新数据

USE AdventureWorks2012;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
--关联其他表进行数据更新
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

# Try-Catch

参考链接

-- Check to see whether this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_LINE () AS ErrorLine
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_MESSAGE() AS ErrorMessage;
GO

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the DELETE statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

    -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state.' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is committable.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;
    END;
END CATCH;
GO

# 使用程序集

参考链接

1.所需命名空间

开发基本 CLR 数据库对象所需的组件随一起安装 SQL Server 。 CLR 集成功能在称作 system.data.dll 的程序集中公开，该程序集是 .NET Framework 的一部分。该程序集还在全局程序集缓存 (GAC) 以及 .NET Framework 目录中提供。对此程序集的引用通常由命令行工具和 Microsoft Visual Studio 自动添加，因此无需手动添加它。

system.data.dll 程序集包含以下命名空间，这些命名空间是编译 CLR 数据库对象所必需的：

System.Data
System.Data.Sql
Microsoft.SqlServer.Server
System.Data.SqlTypes

提示

支持在 Linux 上加载 CLR 数据库对象，但必须用 .NET Framework 生成它们（SQL Server CLR 集成不支持 .NET Core）。此外，Linux 不支持具有 EXTERNAL_ACCESS 或 UNSAFE 权限集的 CLR 程序集。

2.撰写一个简单的“Hello World”存储过程

将以下 Visual C# 或 Microsoft Visual Basic 代码复制并粘贴到某一文本编辑器中，并且将其保存在名为“helloworld.cs”或“helloworld.vb”的文件中。

C#复制

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld(out string text)
    {
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
        text = "Hello world!";
    }
}

这一简单的程序包含针对公共类的单个静态方法。此方法使用两个新类**SqlContext** 和**SqlPipe**，创建托管数据库对象以输出简单的短信。此方法还将字符串“Hello world!”指派为某一输出参数的值。此方法可以声明为 SQL Server 中的存储过程，然后采用与 Transact-SQL 存储过程相同的方式运行。

将此程序编译为库，将其加载到 SQL Server 中，并将其作为存储过程运行。

3.编译 "Hello World" 存储过程

SQL Server 默认情况下将安装 Microsoft .NET Framework 再分发文件。这些文件包括 csc.exe 和 vbc.exe，它们是用于 Visual C# 和 Visual Basic 程序的命令行编译器。为了编译我们的示例，您必须修改路径变量以指向包含 csc.exe 或 vbc.exe 的目录。下面是 .NET Framework 的默认安装路径。

C:\Windows\Microsoft.NET\Framework\(version)

Version 包含已安装 .NET Framework 可再发行组件的版本号。例如：

C:\Windows\Microsoft.NET\Framework\v4.6.1

一旦将 .NET Framework 目录添加到路径后，您可以使用以下命令将该存储过程示例编译到某一程序集中。 /Target选项可用于将其编译为程序集。

对于 Visual C# 源文件：

$csc /target:library helloworld.cs

对于 Visual Basic 源文件：

$vbc /target:library helloworld.vb

以上命令使用 /target 选项启动 Visual C# 或 Visual Basic 编译器，以指定生成库 DLL。

4.在 SQL Server 中加载并运行“Hello World”存储过程

一旦该存储过程示例成功编译后，就可以在 SQL Server 中测试它。为此，打开 SQL Server Management Studio 并创建一个新查询，将其连接到适合的测试数据库（例如，AdventureWorks 示例数据库）。

在 SQL Server 中，能否执行公共语言运行时 (CLR) 代码默认设置为 OFF。可以通过使用sp_configure系统存储过程来启用 CLR 代码。有关详细信息，请参阅 Enabling CLR Integration。

我们将需要创建该程序集，以便可以访问该存储过程。对于此示例，我们将假定您已在 C:\ 目录中创建了 helloworld.dll 程序集。将以下 Transact-SQL 语句添加到您的查询中。

$CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

一旦创建了该程序集后，我们现在就可以通过使用 create procedure 语句访问 HelloWorld 方法。我们将存储过程称为“hello”：

SQL 复制

CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld
-- if the HelloWorldProc class is inside a namespace (called MyNS),
-- the last line in the create procedure statement would be
-- EXTERNAL NAME helloworld.[MyNS.HelloWorldProc].HelloWorld

一旦创建该存储过程后，就可以像用 Transact-SQL 编写的普通存储过程一样运行该存储过程。运行以下命令：

SQL 复制

DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J

这将在 SQL Server Management Studio 消息窗口中导致以下输出。

复制

Hello world!
Hello world!

5.删除“Hello World”存储过程示例

在您完成该存储过程示例的运行后，可以从测试数据库中删除该过程和程序集。

首先，使用 drop procedure 命令删除该存储过程。

SQL 复制

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')
   drop procedure hello

一旦该存储过程删除后，就可以删除包含示例代码的程序集。

SQL 复制

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')
   drop assembly helloworld

# 数据导入

参考链接

有几种方式，直接导入excel(xls,xlsx,csv),BCP,BuilkInsert。