Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create a field using SQL statement

Posted on 2004-09-06
7
Medium Priority
?
249 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
5 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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

581 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