Pass parameter in create table using batch file and SQLCMD

Question

I have to pass a parameter (date) into a create table script that will be triggered via a batch file. I have used SQLCMD and the code is as follows

Batch

for /f "tokens=1-4 delims=/:." %%a in ("%TIME%") do (
       set hh=%%a&set nn=%%b&set ss=%%c&set ms=%%d)
echo "timeset"   
set V_TIMESTAMP=%yyyy%%mm%%dd%
echo %V_TIMESTAMP%

sqlcmd -E -S %V_SERVER% -d %V_DATABASE% -b -i %V_SQL_SCRIPT%  -v timestmp=%V_TIMESTAMP%

SQL

CREATE TABLE [dbo].[tblname_'($timestmp)'](
[PKCol1] [int] NOT NULL)

From what I see that output should a table with the name tblname_20131022 but what I see is a table created with the name tblname_'($timestmp)'

Is there anything wrong in what I have done? Please suggest.

Thank you, Vibhav

May be i'm wrong, but where are %yyyy% %mm% and %dd% defined/initialized ? — MC ND, Oct 22 '13 at 15:08
In the for loop. I pulled those values from the date variable. set hh=%%a&set nn=%%b&set ss=%%c&set ms=%%d — Vibhav MS, Oct 22 '13 at 15:12

score 0 · Answer 1 · answered Oct 22 '13 at 20:55

0

In your CREATE TABLE statement, the dollar sign ($) needs to be outside the parentheses... and get rid of the quotes:

CREATE TABLE [dbo].[tblname_$(timestmp)](
[PKCol1] [int] NOT NULL)

answered Oct 22 '13 at 20:55

SWalters

3,615
5
30
37

Pass parameter in create table using batch file and SQLCMD

1 Answers1