Set Default Values

[NearImpossible]

I'm thinking I already know the answer, but Is it possible to set a multi line default value for a column in a SQL table?

i.e.

Clients:
Servers:

 

[theDBguy]

You mean like include/add the new line character in the data?

 

[NearImpossible]

Whenever I add a new record, I would like the default value for the column to populate with

Clients:
Server:

I'm using Access for the FE and tried to set it on the default value there as well but haven't had any luck so far.

 

[theDBguy]

Whenever I add a new record, I would like the default value for the column to populate with

Clients:
Server:

I'm using Access for the FE and tried to set it on the default value there as well but haven't had any luck so far.

What is the column type?

 

[NearImpossible]

What is the column type?

currently nvarchar(Max) but I can make it whatever as there is no need for formatting

 

[sonic8]

I'm thinking I already know the answer, but Is it possible to set a multi line default value for a column in a SQL table?

Of course it is:

ALTER TABLE yourTableName    
    ADD CONSTRAINT yourDefaultName 
        DEFAULT 'Clients:' + char(13) + char(10) + 'Servers:' 
            FOR yourFieldName ;

But, I think the default values from the server might not show up in your form. So, maybe, you rather want to add the default value to the control in the Access form. Just paste this into the Default Value property:

"Clients:" & Chr(13) & Chr(10) & "Servers:"

 

[theDBguy]

currently nvarchar(Max) but I can make it whatever as there is no need for formatting

Hi. I was able to use the following in SSMS:

alter table test add constraint notes_def default 'client:'+char(13)+char(10)+'server:' for notes;

Edit: Oops, too slow...

 

[NearImpossible]

Thanks to both sonic8 and theDBguy.

Setting it in access didn't work, but running the command in SSMS did the trick.

Thanks again !!

 

[theDBguy]

Thanks to both sonic8 and theDBguy.

Setting it in access didn't work, but running the command in SSMS did the trick.

Thanks again !!

Hi. Glad to hear you got it sorted out. Phil and I were happy to assist. Good luck with your project.

 

[NearImpossible]

Is it possible to setup multiple column defaults?

EX:

Column 1 : Defaults X, Y, Z
Column 3 : Defaults 1,2

I tried using 2 different Alter Tables statements in SSMS, which completed successfully, but now I am getting a "String or binary data would be truncated" error when I try to add a new record

Please advise

 

[NearImpossible]

Disregard,

I was trying to use the wrong column in the FOR statement

 

[theDBguy]

Disregard,

I was trying to use the wrong column in the FOR statement

Can you post your final SQL statement? Thanks for sharing.

 

[NearImpossible]

Can you post your final SQL statement? Thanks for sharing.

I don't have access to it right now but I just basically repeated the whole Alter Table statement for each column that I wanted to specify a default criteria. I just changed the constraint name to match the column, added DEF ,for default, behind it and then specified the criteria. I have 6 columns all with different default criteria now.

ALTER TABLE [COLOR="Red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column1DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' 
            FOR [COLOR="red"]Column1[/COLOR] ;

ALTER TABLE [COLOR="red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column2DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' + char(13) + char(10) + 'Criteria 3:'
            FOR [COLOR="red"]Column2[/COLOR] ;

ALTER TABLE [COLOR="red"]Table1[/COLOR]    
    ADD CONSTRAINT [COLOR="red"]Column4DEF[/COLOR] 
        DEFAULT 'Criteria 1:' + char(13) + char(10) + 'Criteria 2:' + char(13) + char(10) + '    Criteria 3:' + char(10) + '    Criteria 4:'
            FOR [COLOR="red"]Column4[/COLOR] ;

The above query gives me the following output everytime I add a new record:

|   Column1   |   Column2   |   Column3   |     Column4     |
|-------------|-------------|-------------|-----------------|
| Criteria 1: | Criteria 1: |             | Criteria 1:     |
| Criteria 2: | Criteria 2: |             | Criteria 2:     |
|             | Criteria 3: |             |     Criteria 3: |
|             |             |             |     Criteria 4: |
|-------------|-------------|-------------|-----------------|

The one thing I noticed was if I changed anything on the table after running the query, i.e. Adding/Removing/Renaming a column, I had to rerun the query to reassign the values for the above columns, even if they weren't the ones modified.

I was getting the truncating error because the column I originally specified was limited at 20 characters.

 

[theDBguy]

The one thing I noticed was if I changed anything on the table after running the query, i.e. Adding/Removing/Renaming a column, I had to rerun the query to reassign the values for the above columns, even if they weren't the ones modified.

Hi. If you're running the ADD constraint again, it might be a good idea to DROP it first before adding it again. Just a thought...

 

[NearImpossible]

Hi. If you're running the ADD constraint again, it might be a good idea to DROP it first before adding it again. Just a thought...

From what I gathered after adding a new column to the table or renaming an existing column, or changing the type, it automatically dropped everything as I wouldn't get any defaults when adding a new record, unless I reran the query.

Not sure if there's another way of doing it, but I wasn't able to add multiple column defaults without doing the Add Constraint for each each column.

 

[theDBguy]

From what I gathered after adding a new column to the table or renaming an existing column, or changing the type, it automatically dropped everything as I wouldn't get any defaults when adding a new record, unless I reran the query.

Not sure if there's another way of doing it, but I wasn't able to add multiple column defaults without doing the Add Constraint for each each column.

Hi. What do you get if you run this SQL?

SELECT * FROM sys.default_constraints;

You can compare the results after you modify the table to add a new column.

 

[NearImpossible]

Hi. What do you get if you run this SQL?

SELECT * FROM sys.default_constraints;

You can compare the results after you modify the table to add a new column.

I'll test it tomorrow, both before and after and report back!!

 

[theDBguy]

I'll test it tomorrow, both before and after and report back!!

Sounds good. Thanks!

 

[NearImpossible]

As I suspected, the constraints specified in the query disappeared after adding a new column, however anything set with a default value in the table setup, i.e. bit field set to not allow nulls and a default value of ((0)) remained. (see attachments)

On a side note, I noticed there are still several of the defaults listed that were setup on a table, that no longer exists. Is it possible to clear those out or will it affect anything if i just leave them alone?