在 SQL Server 中插入存储过程

如何在 SQL 存储过程中编写 INSERT 语句？或者如何通过示例编写 INSERT 存储过程。在本演示中，我们将使用一个空的 EmployeeDup 表。

以及 Employee 表

Employee Table

SQL Server 中的插入存储过程示例

在此 Server 示例中，我们将向您展示如何在存储过程中使用 INSERT 语句。我建议您参考 SP 简介 文章来了解基础知识。

IF OBJECT_ID ( 'InsertStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFirstExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFirstExample

AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	VALUES ('Tutorial', 'Gateway', 'Education', 10000, 200)
          ,('Imran', 'Khan', 'Skilled Professional', 15900, 100)
          ,('Doe', 'Lara', 'Management', 15000, 60)
          ,('Ramesh', 'Kumar', 'Professional', 65000, 630)

END
GO

从上面的代码片段中，您可以看到我们正在向空的 EmployeeDup 表中插入四行。运行上面的 sp。

Messages
--------
Command(s) completed successfully.

让我使用 EXEC 命令（执行命令）来执行存储过程以检查结果

EXEC [dbo].[InsertStoredProcedureFirstExample]
GO

执行上面的查询

Messages
--------
Command(s) completed successfully.

现在，让我们看看存储过程的执行是否将新记录插入到我们的 EmployeeDup 表中

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
Select Records from table 4

SQL Server 中的插入存储过程示例 2

本示例展示了如何在存储过程中使用 SELECT 语句 和 INSERT 语句。从下面的代码片段中，您可以看到我们正在使用 INSERT INTO SELECT 语句 将 Employee 表中的所有记录插入到 EmployeeDup 表中。

IF OBJECT_ID ( 'InsertStoredProcedureSecondExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureSecondExample;  
GO

CREATE PROCEDURE InsertStoredProcedureSecondExample
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
					   FROM [Employee]
END
GO

运行查询

Messages
--------
Command(s) completed successfully.

让我执行 sp 来加载值

EXEC [dbo].[InsertStoredProcedureSecondExample]
GO
INSERT Stored Procedure in SQL Server 6

接下来，写下以下查询以检查我们是否插入了记录。

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
Check records 7

SQL Server 插入存储过程与 WHERE 子句

在本示例中，我们将向您展示如何在存储过程中使用 WHERE 子句 和 INSERT INTO SELECT 语句。从下面的代码可以看出，该过程应将 Employee 表中销售额大于 3400 的所有记录插入到 EmployeeDum 中。

IF OBJECT_ID ( 'InsertStoredProcedureThirdExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureThirdExample;  
GO

CREATE PROCEDURE InsertStoredProcedureThirdExample
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
			   FROM [Employee]
			   WHERE [Sales] > 3400
END
GO

运行上面的带 WHERE 子句的 INSERT 存储过程查询

Messages
--------
Command(s) completed successfully.

在执行此存储过程之前，我们 截断 了 EmployeDum 表以了解记录。让我们使用 EXEC 命令来执行存储过程

EXEC [dbo].[InsertStoredProcedureThirdExample]
GO
INSERT Stored Procedure 9

让我们运行以下查询，看看存储过程是否插入了销售额大于 3400 的记录。

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
View Employee table 10

SQL 存储过程中的带参数的 INSERT 语句

本示例向您展示如何创建带参数的 INSERT 存储过程。

IF OBJECT_ID ( 'InsertStoredProcedureFourthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFourthExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFourthExample
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
			   FROM [Employee]
			   WHERE [Occupation] = @Occupation
END
GO

运行上面带参数的 sp 查询

Messages
--------
Command(s) completed successfully.

让我执行存储过程。正如您所见，我们有 @Occupation 参数。因此，让我们使用以下任何一种方式传递参数值

EXEC [dbo].[InsertStoredProcedureFourthExample] N'Professional';
GO
--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFourthExample] N'Management';
GO
INSERT Stored Procedure in SQL Server 12

让我们看看插入的数据

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
Select loaded records 13

SQL 存储过程中的带多个参数的 INSERT 语句

在本示例中，我们将在存储过程中的 INSERT 语句along with 语句中使用多个参数。

IF OBJECT_ID ( 'InsertStoredProcedureFifthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFifthExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFifthExample
	@Education VARCHAR(50),
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
					   FROM [Employee]
					   WHERE [Education] = @Education OR [Occupation] = @Occupation
END
GO

运行上面带多个参数的 sp 查询

Messages
--------
Command(s) completed successfully.

以下是执行存储过程的多种方法。让我执行 sp。

EXEC [dbo].[InsertStoredProcedureFifthExample] @Education = N'Bachelors', @Occupation = N'Clerical';
GO
--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] @Occupation = N'Clerical',  @Education = N'Bachelors';
GO

--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] N'Bachelors', N'Clerical';
GO

使用下面显示的查询来检查插入的结果。

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
Check Employee Table 16