Pages

Friday, March 5, 2010

Dynamically passing variables to .sql through bat file

runscript.bat
REM echo %2 >> log\all.log
sqlcmd -S -d -E -i upgrade.sql -olog\upgrade%2.log -v servername=%1 sitecode=%2
type log\upgrade%2.log >> log\all.log

:END

upgrade.sql:
DECLARE @vsql NVARCHAR(4000)

SET @vsql = ''
Declare @serverName NVARCHAR(255)
Declare @sitecode NVARCHAR(50)
set @serverName = '$(servername)'
set @sitecode = '$(sitecode)'
Print @serverName
Print @sitecode


SET @vsql = 'SET IDENTITY_INSERT dbo.[] ON
insert into dbo.[GL_Site_SourceTests]
(,)
select distinct ,
from ['+@serverName +'].'+ @sitecode +'_dmss.dbo.[] i
where not exists (select 1 from dbo.[] s
where i.site_tk = s.site_tk
and i.gl_site_sourceTest_tk = s.gl_site_sourceTest_tk)
SET IDENTITY_INSERT dbo.[] OFF'


--Print @vsql
EXEC sp_executesql @vsql

No comments:

Post a Comment