在批处理文件中传递数据库名称sql server
问题描述:
这是我的SQL代码。我已经尝试过了2种方式无一不是给我的错误在数据库名称在批处理文件中传递数据库名称sql server
不正确的语法附近 'Intake_Bak_09_04_15_07_15'
方法1
INSERT INTO Inspectors (InspectorId, FirstName, LastName, UserName, ReplicationId)
SELECT
InspectorId, FirstName, LastName, UserName, ReplicationId
FROM N'$(oldDBName)'.dbo.Inspectors
INSERT INTO MachineSettings (Name, Value)
SELECT Name, Value
FROM N'$(oldDBName)'.dbo.MachineSettings
WHERE Name = 'MachineId'
方法2
DECLARE @InsSql VARCHAR(500), @MacSql VARCHAR(500)
SET @InsSql = 'INSERT INTO Inspectors (InspectorId, FirstName, LastName, UserName, ReplicationId) SELECT InspectorId, FirstName, LastName, UserName, ReplicationId FROM N['$(oldDBName)'].dbo.Inspectors'
PRINT @InsSql
EXEC (@InsSql)
SET @MacSql = 'INSERT INTO MachineSettings (Name, Value) SELECT Name, Value FROM N['$(oldDBName)'].dbo.MachineSettings WHERE Name = ''MachineId'''
PRINT @MacSql
EXEC (@MacSql)
这是我正在使用的SQLCMD
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
for /f "tokens=1-3 delims=: " %%i in ("%time%") do (
set hour=%%i
set min=%%j
set sec=%%k
)
SQLCMD -S . -d Intake -i TransferSQL.Sql -b -v oldDBName=Intake_Bak_%month%_%day%_%year%_%hour%_%min%
任何帮助表示赞赏。
在此先感谢
答
这是工作的语法,感谢保罗
INSERT INTO Inspectors (InspectorId, FirstName, LastName, UserName, ReplicationId) SELECT InspectorId, FirstName, LastName, UserName, ReplicationId FROM [$(oldDBName)].dbo.Inspectors
INSERT INTO MachineSettings (Name, Value) SELECT Name, Value FROM [$(oldDBName)].dbo.MachineSettings WHERE Name = 'MachineId'
的DBNAME不能为VARCHAR。尝试使用'[$(oldDBName)]'而不是'N ['$(oldDBName)']' – Paolo
真棒,它工作。非常感谢你 –