ALTER TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ALTER TABLE

Author

Topic

yvartan
Starting Member

1 Post

Posted - 2014-10-06 : 13:07:30

I am having a problem adding new column to a table via SQLCMD
here is the code
---------------------------------------------------------------------

:r ServerParam.sql
:connect $(sqlServer) -U $(sqlUser) -P $(sqlPass)

DECLARE @cmd varchar(5000)
DECLARE @SServer varchar(1000)
DECLARE @database varchar(1000)
DECLARE @tableName varchar(500)

set @database='CalJobsErrors'
SET @SServer = '$(inputSrc)'
SET @tableName = '$(subgt)'+'_'+'$(tblName)'

PRINT '------------------------------------------------'
PRINT ' Start processing Table: ' + @database + '.dbo.' + @tableName
PRINT '------------------------------------------------'
PRINT ' '

IF NOT EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = @database)
BEGIN
PRINT ' Creatin database: ' + @database
SET @cmd = 'CREATE DATABASE ' + @database
exec(@cmd)
END

SET DATEFORMAT MDY

IF object_id(@database + '.dbo.' + + @tableName) is null
BEGIN
PRINT ' Creating Table: '+ @database + '.dbo.' + @tableName

SET @cmd = 'CREATE TABLE ' + @database + '.dbo.' + @tableName +
'(
Column0 varchar(50) null,
Column1 varchar(50) null,
Column2 varchar(50) null,
Column3 varchar(50) null,
Column4 varchar(50) null,
Column5 varchar(50) null,
Column6 varchar(50) null,
Column7 varchar(50) null,
Column8 varchar(50) null,
Column9 varchar(50) null,
Column10 varchar(50) null,
Column11 varchar(50) null,
Column12 varchar(500) null,
)'
EXEC(@cmd)
END
ELSE
BEGIN
PRINT ' Deleting Table: '+ @database + '.dbo.' + @tableName
SET @cmd = 'DELETE FROM ' + @database + '.dbo.' + @tableName
EXEC(@cmd)
END

BEGIN
PRINT ' Inserting/Refreshing Data in Table: '+ @database + '.dbo.' + @tableName
SET @cmd = 'BULK INSERT ' + @database + '.dbo.' + @tableName + '
FROM ' + char(39) + @SServer + '$(subgt)' + '\ErrorFile\' + '$(txtFile)' + char(39) +
' WITH
(
CODEPAGE=''RAW'',
DATAFILETYPE=''char'',
FIELDTERMINATOR=''|'',
ROWTERMINATOR = '''+char(10)+'''
)'

EXEC(@cmd)
END

BEGIN

SET @cmd = 'ALTER TABLE ' + @database + '.dbo.' + @tableName + '
ADD (LoadDate varchar(8) null,
processDate datetime null)'
PRINT @cmd

exec @cmd

END

---------------------------------------------------------------------

when I am running this script, it creates the database and the table but when I am trying to add the last to field vi ALTER TAABLE ADD
I am getting error
----------------------------------------------------------------------
C:\CalJobs\SQLCMD\SQLScripts>SQLCMD -v subgt=SJI txtFile=LTS_ERROR_10022014.TXT
tblName=LTS_ERROR_10022014 -i CreateErrorTableSQL.sql
Sqlcmd: Successfully connected to server 'VULCAN'.
------------------------------------------------
Start processing Table: CalJobsErrors.dbo.SJI_LTS_ERROR_10022014
------------------------------------------------

Deleting Table: CalJobsErrors.dbo.SJI_LTS_ERROR_10022014

(88 rows affected)
Inserting/Refreshing Data in Table: CalJobsErrors.dbo.SJI_LTS_ERROR_10022014
ALTER TABLE CalJobsErrors.dbo.SJI_LTS_ERROR_10022014
ADD (LoadDate varchar(8) null,
processDate datetime null)
Msg 911, Level 16, State 4, Server VULCAN, Line 77
Database 'ALTER TABLE CalJobsErrors' does not exist. Make sure that the name is
entered correctly.

C:\CalJobs\SQLCMD\SQLScripts>

Vartan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 13:37:38

Try adding a GO right before the ALTER TABLE. Alternatively you can run 2 sqlcmd commands, referencing different scripts, and then wrap them in a cmd file.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-06 : 13:48:32

Why do you have parentheses around your column definition? Can you print out your ALTER TABLE ADD Column code and get it to work independently? Try putting parentheses around your variable in the exec statement (exec (@cmd);). Better yet, use "exec sp_ExecuteSQL @cmd" instead of "exec @cmd".

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources