在SQL Server 2008中,如何将数据从一个数据库复制到另一个数据库?
我正在尝试编写一个存储过程,将数据子集从一组表复制到不同数据库中的一组相同的表。 “源”数据库需要是存储过程的参数。
我已经为此奋斗了两天,我认为我有一个很好的解决方案:
- 验证模式是否相同。
- 使用动态 SQL 为源表创建临时“rmt”同义词。
- 使用 INSERT INTO A SELECT * FROM rmtA WHERE
复制数据 - 删除同义词。
这对于大多数表来说效果很好,但是对于包含标识列的表,我不仅被迫 SET IDENTITY_INSERT ON &关闭,但更糟糕的是,我不能使用 SELECT *;我必须明确指定所有列。如果我稍后添加或删除列,这将是一场噩梦。
我必须把一些东西拿出来,所以我现在就使用这个解决方案,但我想认为在某个地方有更好的解决方案。
帮助?
I'm trying to write a stored procedure to copy a subset of data from one set of tables to an identical set of tables in a different database. The "source" database needs to be a parameter to the stored procedure.
I've struggled with this for two days now, and I thought I had a good solution:
- Validate that the schemas are the same.
- Create temporary "rmt" synonyms for the source tables using dynamic SQL.
- Copy the data using INSERT INTO A SELECT * FROM rmtA WHERE <criteria>
- Delete the synonyms.
This works pretty well for most tables, but for tables that contain an identity column, I'm forced not only to SET IDENTITY_INSERT ON & OFF, but even worse, I can't use SELECT *; I have to specify all the columns explicitly. This will be a nightmare if I add or delete columns later.
I've gotta get something out the door, so I'm going with this solution for now, but I'd like to think that there's a better solution out there somewhere.
Help?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
听起来您在存储过程中使用了动态 SQL,因此您已准备好在 SELECT 子句中动态创建列列表。
您可以从 sys.columns 中选择以获取列列表,并了解表是否具有标识列。下面的查询显示了创建列列表所需的信息。
简而言之,如果至少一列的
is_identity
为 1,则您需要包含SET IDENTITY_INSERT
。并且,您可以从 SELECT 子句中排除is_identity
= 1 的任何列。而且,此方法将适应您添加到表中的新列。
这是一个例子
It sounds like you're using dynamic SQL in your stored procedure, so you're ready to dynamically create your list of columns in the SELECT clause.
You can select from sys.columns to get the list of columns, and learn if the table has an identity column. Here's a query that shows the information you need to create the list of columns.
In short, if
is_identity
is 1 for at least one column, you'll need to include theSET IDENTITY_INSERT
. And, you would exclude any columns from the SELECT clause whereis_identity
= 1.And, this approach will adapt to new columns you add to the tables.
Here's an example