?
Solved

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

Posted on 2009-04-13
10
Medium Priority
?
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
10 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
Back Up Your Microsoft Windows Server®

Back up 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.

 

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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