Solved

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

Posted on 2009-04-13
10
233 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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: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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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