在 SQL 中将数据库名称设置为变量
目前，我正在根据一些业务规则在一个数据库到另一个数据库之间进行一些数据迁移.
currently, I'm doing a bit of a data migration between one db to another based on some business rules.
我有一个巨大的脚本，我正在编写它，我在其中多次引用了两个数据库.问题是这种数据迁移目前正在开发中，在某些时候，我想在生产环境中使用两个不同的数据库进行迁移.
I have this huge script that I'm writing where I'm referencing both databases a lot of times. The problem is that this data migration is currently in development, at some point I am going to want to have to do it in production with two different databases.
而不是像这样直接引用数据库名称
Instead of referencing the database name directly like so
Insert Into Database2.dbo.Table1
Select * from Database1.dbo.Table1
我想以某种方式在脚本开始时引用数据库.这样我就可以在更改数据库时只更改一个变量.
I would like to somehow just reference the database at the start of the script. So that I can just change the one variable when I change databases.
这可能吗?
推荐答案
使用 SQLCMD变量:
:setvar dbfrom database1
:setvar dbto database2
Insert into [$(dbto)].dbo.Table1
select * from [$(dbfrom)].dbo.Table2;
此语法适用于 sqlcmd.exe(来自脚本)以及 Management Studio，请参阅 使用查询编辑器编辑 SQLCMD 脚本.脚本还允许您从命令行设置变量:
This syntax works in sqlcmd.exe (from scripts), as well as in Management Studio, see Editing SQLCMD Scripts with Query Editor. Scripting also allows you to set the variables from the command line:
sqlcmd /E /S server /i script.sql /v dbfrom=database1 /v dbto=database2
我还有一个库，允许您使用来自应用程序的 sqlcmd 变量和脚本作为 Google 代码的来源.
I also have a library that allows you to use sqlcmd variables and scripts from applications available as source on Google Code.
相关文章