問題描述
目前，我正在根據(jù)一些業(yè)務(wù)規(guī)則在一個數(shù)據(jù)庫到另一個數(shù)據(jù)庫之間進(jìn)行一些數(shù)據(jù)遷移.
currently, I'm doing a bit of a data migration between one db to another based on some business rules.
我有一個巨大的腳本，我正在編寫它，我在其中多次引用了兩個數(shù)據(jù)庫.問題是這種數(shù)據(jù)遷移目前正在開發(fā)中，在某些時候，我想在生產(chǎn)環(huán)境中使用兩個不同的數(shù)據(jù)庫進(jìn)行遷移.
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.
而不是像這樣直接引用數(shù)據(jù)庫名稱
Instead of referencing the database name directly like so
Insert Into Database2.dbo.Table1
Select * from Database1.dbo.Table1
我想以某種方式在腳本開始時引用數(shù)據(jù)庫.這樣我就可以在更改數(shù)據(jù)庫時只更改一個變量.
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 腳本.腳本還允許您從命令行設(shè)置變量:
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
我還有一個庫，允許您使用來自應(yīng)用程序的 sqlcmd 變量和腳本作為 Google 代碼的來源.
I also have a library that allows you to use sqlcmd variables and scripts from applications available as source on Google Code.
這篇關(guān)于在 SQL 中將數(shù)據(jù)庫名稱設(shè)置為變量的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！