Solved

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

Posted on 2009-04-13
10
214 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

20 Experts available now in Live!

Get 1:1 Help Now