Solved

Create a field using SQL statement

Posted on 2004-09-06
7
239 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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