Solved

Create a field using SQL statement

Posted on 2004-09-06
7
240 Views
Last Modified: 2006-11-17
I want to create a Char field of size 50 called "Label" into a table and set the default value to "12M".  I do not want Nulls allowed and I would like to add it to the primary key.  

What SQL statement, or series of statements ran one after the other, would let me accomplish this?

Thanks.
0
Comment
Question by:pansophy
  • 3
7 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11991738
--Get the name of the primary key constraint
sp_helpconstraint test


--drop the current primary key
ALTER TABLE TableA DROP CONSTRAINT NameOfPrimaryKeyConstraint
GO

--add the new column
ALTER TABLE TableA ADD Label char(50) CONSTRAINT Label_default DEFAULT '12M'
GO

--recreate a primary key that includes the new column
ALTER TABLE TableA ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
   (OriginalColumnForPrimaryKey, Label) ON PRIMARY
GO
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11991744
Where 'test' is the name of your table:

sp_helpconstraint test
0
 

Author Comment

by:pansophy
ID: 11991884
Okay, I want to do this using ADO.  Here is what I have but I get an error.  It doesn't seem happy with NameOfPrimaryKeyConstraint

Dim rstTableList, rstTemp As ADODB.Recordset

Dim varTableList() As Variant
Dim RrdCount As Integer
Dim intCount, i As Integer
Dim strRule As String
Dim strTable As String


Set rstTableList = New ADODB.Recordset
rstTableList.Open "tblDataEntryTableNames", CurrentProject.Connection

    rstTableList.MoveFirst
    varTableList() = rstTableList.GetRows
    RrdCount = UBound(varTableList, 2)
   
If varTableList(0, i) = "ftbl12M_CESD" Then
    For i = 0 To RrdCount - 1
        'strSQL = "Alter Table " & varTableList(0, i) & " alter column ID Int;"
           
   
        '    --drop the current primary key
        strSQL1 = "ALTER TABLE " & varTableList(0, i) & " DROP CONSTRAINT NameOfPrimaryKeyConstraint"
        'GO
       
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL1)
        DoCmd.SetWarnings True
       
        '--add the new column
        strSQL2 = "ALTER TABLE " & varTableList(0, i) & " ADD Label char(50) CONSTRAINT Label_default DEFAULT '12M'"
        'GO
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL2)
        DoCmd.SetWarnings True
       
        '--recreate a primary key that includes the new column
        strSQL3 = "ALTER TABLE " & varTableList(0, i) & " ADD CONSTRAINT ID PRIMARY KEY CLUSTERED (OriginalColumnForPrimaryKey, Label) ON PRIMARY"
        'GO
        DoCmd.SetWarnings False
        DoCmd.RunSQL (strSQL3)
        DoCmd.SetWarnings True
   
       
    Next
Else
End If

End Sub
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11992092
Sorry, I expected you to run these queries manually and replace NameOfPrimaryKeyConstraint and OriginalColumnForPrimaryKey with values you got from running sp_helpcontraint.

Do you just need to do this one time, or do you need to be able to do it every time someone runs a function of your application? Also, are you running SQL Server or just MSDE? I ask because it'll be easier to write T-SQL to do it once and describe how to execute it in Query Analyzer than to do it in VBA.
0
 
LVL 7

Accepted Solution

by:
FDzjuba earned 500 total points
ID: 11992170
SQL would be something like:
ALTER TABLE table1 ADD [label] char(50) NOT NULL CONSTRAINT AddDateDflt DEFAULT '12M' WITH VALUES

try this

Dim rstTableList, rstTemp As ADODB.Recordset

Dim varTableList() As Variant
Dim RrdCount As Integer
Dim intCount, i As Integer
Dim strRule As String
Dim strTable As String


Set rstTableList = New ADODB.Recordset
rstTableList.Open "tblDataEntryTableNames", CurrentProject.Connection

    rstTableList.MoveFirst
    varTableList() = rstTableList.GetRows
    RrdCount = UBound(varTableList, 2)
   
If varTableList(0, i) = "ftbl12M_CESD" Then
    For i = 0 To RrdCount - 1
        strSQL = "ALTER TABLE " & varTableList(0, i) & " ADD [label] char(50) NOT NULL CONSTRAINT AddDateDflt DEFAULT '12M' WITH VALUES"
           rstTableList.EXECUTE(strSQL)
    Next
Else
End If

End Sub
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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