Solved

T-SQL to Add Columns to a SQL Server Database Table

Posted on 2009-04-13
10
224 Views
Last Modified: 2012-08-13
      

Due to Compliance issues, we need to provide scripts that are run in Query Analyzer to add new fields to SQL Tables

Here are the particulars for adding the fields

Server Name - CCCAPP2000
Database Names - APSR

SQL Table Name - Tbl_Daily_Assignments
Field Name - CustPhone_Number_Ext Data Type nvarchar(6)
Field Name - AgencyPhone_Number_Ext Data Type nvarchar(6)

SQL Table Name - LPINFO
Field Name - CustPhone_Number_Ext Data Type nvarchar(6)
Field Name - AgencyPhone_Number_Ext Data Type nvarchar(6)

Could someone provide me with an example of the Transact SQL I need to add these fields?
0
Comment
Question by:charming
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 125 total points
ID: 24129396
alter table Tbl_Daily_Assignments
add CustPhone_Number_Ext Data Type nvarchar(6),
add AgencyPhone_Number_Ext Data Type nvarchar(6)

alter table LPINFO
add CustPhone_Number_Ext Data Type nvarchar(6),
add AgencyPhone_Number_Ext Data Type nvarchar(6)
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24129399
charming,

One thing you may want to look at for this sort of application is

Automatically Generating Change Scripts from SSMS (see link)  it will automatically log anything you do graphically.

http://www.mssqltips.com/tip.asp?tip=1723
0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24129402
use
alter table Tbl_Daily_Assignments add [CustPhone_Number_Ext Data]  nvarchar(6)
alter table Tbl_Daily_Assignments add [AgencyPhone_Number_Ext Data]  nvarchar(6)

LPINFO
alter table LPINFO add [CustPhone_Number_Ext Data]  nvarchar(6)
alter table LPINFO add [AgencyPhone_Number_Ext Data ]  nvarchar(6)
0
 

Author Comment

by:charming
ID: 24129424
Do I need to specify the Server and Database Name in the Scripts, since I need to run this for the same Table Name on various Servers which point to Development, Certification and Production?  If so how?  Thanks
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 60

Expert Comment

by:chapmandew
ID: 24129431
No, no reason to specify the server name...you can specify the db name in your script, but you won't need to so long as you connect to the correct db when you run the script.
0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24129433
if you are logging on  to the same server and database then not required.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24129435
charming,

the scripts would be the same for each database, if the databases are on the same server you could prefix the entire script with "use databasename"

0
 

Author Comment

by:charming
ID: 24129463
I was confused because in reality, I have to send  these scripts for the 3 environments and all 3 have a slightly different Server and Database Name (The Table Names are the same) and I wasn't sure how to distinguish these scripts for each environment for the DBA's

Development
Server Name - CCCAPP2000
Database Names - APSR

Certification
Server Name - CertAPP2000
Database Names - cert_APSR

Production
Server Name - prodAPP2000
Database Names -prod_APSR
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24129530
so long as the table names are the same, it shouldn't matter...you'll still have to connect to the server and database differently each time.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now