Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create a field using SQL statement

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

963 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