从存储过程复制表的数据

Question

我正在学习如何使用 SQL 和存储过程。我知道语法不正确：

使用存储过程将数据从一个表复制到另一个数据库上的另一个表中。问题是我不知道要复制到哪个表或哪个数据库。我希望它使用参数而不是专门指定列。

我有 2 个数据库（Master_db 和 Master_copy），每个数据库上都有相同的表结构。

我想快速选择 Master_db 中的一个表并将该表的数据复制到同名的 Master_copy 表中。我想出了这样的事情：

USE Master_DB
CREATE PROCEDURE TransferData
DEFINE @tableFrom, @tableTo, @databaseTo;

INSERT INTO @databaseTo.dbo.@databaseTo
SELECT * FROM Master_DB.dbo.@tableFrom
GO;

---

我能得到的最接近的就是这个。现在的问题是我有一个时间戳列，并且出现错误：

“无法将显式值插入时间戳列。使用带有列列表的 INSERT 来排除时间戳列，或将 DEFAULT 插入时间戳列。”

USE Kenneth_Integration
GO
CREATE PROCEDURE TransferData
  @table  SYSNAME
  ,@databaseTo SYSNAME
AS
DECLARE @sql NVARCHAR(MAX)

SET @sql = 'DELETE FROM ' + @databaseTo + '..' + @table + ' set identity_insert ' + @databaseTo + '.dbo.' + @table + 
    ' on INSERT INTO ' + @databaseTo + '.dbo.' + @table + ' SELECT * FROM Kenneth_Integration.dbo.' + @table

EXEC sp_executesql @sql
GO

如何使用此 SP 解决此错误？

Answer 1

SQL 查询中的对象名称无法按照您尝试的方式进行参数化。

要使其正常工作，您需要使用动态 SQL：

USE Master_DB
CREATE PROCEDURE TransferData
 @tableFrom   SYSNAME
 ,@tableTo    SYSNAME
 ,@databaseTo SYSNAME
AS
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'INSERT INTO ' + @databaseTo+ '.dbo.' + @tableTo + '
            SELECT * FROM Master_DB.dbo.' + @tableFrom

EXEC sp_executesql @sql
GO;

Answer 2

只有动态 SQL 允许变量作为表名。例如0le：

create procedure dbo.TransferData(
    @src varchar(50),
    @dest varchar(50))
as
declare @query varchar(500)
set @query = 'INSERT INTO ' + @src +
    'SELECT * FROM ' + @dest
exec @query
go

Answer 3

我同意@EdHarper 的观点，构建一个字符串然后运行 ​​sp_executesql 可能是你最好的答案。

需要记住的两件事是我在前面的答案中看不到的：

1）您之前可能已经将数据复制到 Master_copy 数据库表中，因此您可能需要在开始复制之前删除目标表中的数据手术。

2) 如果 Master_copy 表中有任何标识列（例如，因为您使用相同的 Sql 脚本生成 Master_db 和 Master_copy 表），那么您需要确保可以覆盖标识值（这可以通过 在命令上设置identity_insert）。

因此，作为替代方案（没有必要重复回答）并且当您知道数据库名称时，您可以简单地对该方法进行非规范化并为每个表编写一个简单的存储过程。

假设您有一个名为“person”的表，您可以

create procedure TransferDataPerson 
as

delete from Master_copy..Person

set identity_insert Master_copy..Person on

insert into Master_copy..Person
select * from Master_db..Person

为每个表编写：...等等。

Answer 4

我的建议是编写返回表的函数
比使用 insert select 语句

过程和函数之间的区别比你不能写
选择过程但是
你可以写
选择功能