Results 1 to 5 of 5

Thread: Simple question

  1. #1
    Join Date
    Nov 2002
    Posts
    30

    Unanswered: Simple question

    OK, so I don't really do SQL Server, but I need to set a default text value in a column on a table. Should be easy, right? Obviously not so for me.

    The field is a text field, max 16 chars, I have set don't allow nulls and I want the field to default to "None Reported" when new records are created.

    How can I do this?

    I've tried ("None Reported") in the Default Value column and it won't save. Using ('None Reported') allows me to save the table, but doesn't insert the default into the row.

    Sorry to be such a moron but in my 2 SQL Server books it don't seem to address such a simple issue.

    Thanks for your help!
    FlyGirl

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    How are you inserting ? If you are using enterprise manager - you will have to refresh to see the defaults being populated. If you use query analyzer when you re-select you will see them.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    I think you meant to use the char or varchar data types not the text data type.

    This is how a text data type is defined within sql server:

    text
    Variable-length non-Unicode data with a maximum length of
    2^31 - 1 (2,147,483,647) characters.

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Arrow Re: Simple question

    I want a column to default to 'None Reported' when new records are created.

    Q1 How can I do this?
    Consider using char, varchar, nchar, nvarchar columns for character data (text columns are more appropriate for very large character data >> 4000 - 8000 characters).

    A1 You can use Alter Table to add a default constraint to _char columns. for example:
    ALTER TABLE [dbo].[YourTableName]
    ADD CONSTRAINT [Default_ForYourVarCharDataColumnName] DEFAULT ('None Reported') FOR [YourVarCharDataColumnName]

    To work with a detailed example run the following in tempdb:

    Use TempDB
    Go

    CREATE TABLE [Test] (
    [IDColumn] [int] IDENTITY (1, 1) NOT NULL ,
    [VarCharData] [varchar] (32) NOT NULL,
    [InsertedBy] [varchar] (32) NOT NULL
    )
    GO

    SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]

    -- insert some values:
    INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
    VALUES('Mary had a lamb.', 'Tom')
    INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
    VALUES('The lamb had a fleece.', 'Dick')
    INSERT INTO [tempdb].[dbo].[Test]([VarCharData], [InsertedBy])
    VALUES('The fleece was white as snow.', 'Harry')
    Go
    -- the following insert should fail (no default exists for VarCharData)
    INSERT INTO [tempdb].[dbo].[Test]([InsertedBy])
    VALUES('Joe')
    Go
    -- view table rows
    SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]

    -- Now add the 'None Reported' default (a constraint for the [VarCharData] column)
    ALTER TABLE [dbo].[Test] ADD CONSTRAINT [Default_Test_VarCharData] DEFAULT ('None Reported') FOR [VarCharData]
    Go

    -- the following should now no longer fail (the default 'None Reported' should appear in the [VarCharData] column)
    INSERT INTO [tempdb].[dbo].[Test]([InsertedBy])
    VALUES('Joe')
    Go

    -- view table rows Note: (the default 'None Reported' should appear in the [VarCharData] column)
    SELECT [IDColumn], [VarCharData], [InsertedBy] FROM [tempdb].[dbo].[Test]
    Last edited by DBA; 12-04-02 at 01:05.

  5. #5
    Join Date
    Nov 2002
    Posts
    30
    Hey guys, thanks for the help. I'm afraid you have confirmed my idea that simple is not always what a DBA looks for--AUGH!

    I am actually trying to make the change via the MS Access (OK, so I've blown my cover and you know I'm a complete hack) interface which gives me a table designer view of SQL Server. Those nice guys at Microsoft (Gack!) try to make it look like I don't actually have to write SQL, stored proceedures or scripts to do this.

    I do also have access to Enterprise Manager.

    I'll see if I can make sense out of your suggestions. Wish me luck!
    FlyGirl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •