?
Solved

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

Posted on 2009-04-13
10
Medium Priority
?
296 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
9 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 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:udaya kumar laligondla
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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
 
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:udaya kumar laligondla
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

840 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