Adding a default value to a table

I'm new to SQL server and am in the process of converting an Access Db to SQL.

What I'd like to happen is for the current date and an Autonumber generated as soon as the user enters a value in another field. This was fairly straightforward in Access. When I try to set the default in design table (inSQL), the date() is not available. After a little research I came up with CREATE DEFAULT and Bind column commands, Global variables, etc.

I have a couple of questions for anyone that might know:

Is there an easier way than this to create default values in a table? AND,

Would I also have to bind columns for Table lookups? For example, if I wanted a price to be returned after the product ID was entered (Automatic in Access if relationship/form setup properly), would I have to bind the column? Or would it happen automatically?you can use getdate() or current_timestamp instead of date()

for "lookup" feature of access you can create calculated field.

Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Adding a BIT column - 100+ hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?ALTER TABLE ADD...NOT NULL DEFAULT... is a fully logged operation. The logs that contain internal page updates are small but numerous (and I use this word loosely). There is nothing you can do at this point because as you mentioned correctly, - the rollback will take even longer. And as I stated in one of my replies to you previous similar post, - if you kill the service the database recovery will take forever (weeks possibly). There is however a rather drastic approach that may (!!!) yield a quick return your database to pre-ALTER state...I would not recommend you trying it simply because it may or may not succeed, and if it doesn't, - someone is gonna be fired...|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......
I'd hope so...Did they?|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?

From a design perspective, the column should be non-nullable. I'm sure we could deal with it as a NULLable column if absolutely necessary. That's a sacrifice I'd easily be willing to make but at this point it's too late.

As I said, I didn't do this and it's frustrating from my perspective. We really didn't need the column on this table in the first place and it was a without question a bad move. If he started both column add operations at the same time before he realized what a drastic operation this would be then I can understand his perspective. Believe me, I am using the info I learn here, and trying to convey what I learn to my team.

Thanks you both for the feedback! I will not cancel, let it run, just cross my fingers that it finishes in the next week, and I will try to convince my boss to give me yet another database server so that we can start over (not fun) as a parallel strategy. Thankfully, this was done on a secondary system where we are trying to reconstruct our primary database with a slightly improved process (bug fixes and better data gathering).|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......

LOL! No, no backups.

For some reason I feel way more stressed about this than either my boss or the worker who is assisting on this project.|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?

"The other" way? :) Mysterious...

I just emailed our part time DBA but I'm not counting on much. Unfortunately, we are mostly programmers.|||Threads like this just make me speechless.

It's hard to talk when you're rolling on the floor laughing.sql

Add New Record into table

Hi , All
I default value in textbox for show now date and I used to method

txtDate.Text = Now.ToShortDateString

It's have problem when I want to add new record in table

I get error message "The statement has been terminated. String or binary data would be truncated. "

and I set type in database is char length 10 ,I can't set type is datetime because in SQL Server fix type datetime

It's have length 8 but 8 length it's not enough for default value
Please help me
Thanks
Nisarat

First use DateTime.Now.ToShortDateString
Secondly use 'yyyy-MM-dd' eg. '2005-06-01'(notice the single quote ' ) format while inserting data.
|||

Hi, dinuj

I set type of field date is datetime and set default value "dd/mm/yyyy" but I get error message "Error validating the default for column "datekey""
Thanks
Nisarat

|||Do mean to say that you are trying to set a default date for the coloumn datekey
|||

Hi,Dinuj

yes ,I trying set type of field DateKey is DateTime type but ,in SQLServer it's 8 char only and I will trying to defaulf value in this field but It's can not
Thanks
Nisarat

|||

Nisarat wrote:

Hi,Dinuj

yes ,I trying set type of field DateKey isDateTime type but ,in SQLServer it's 8 char only and I willtrying to defaulf value in this field but It's can not
Thanks
Nisarat

Is your DateKey column is of DateTime type? DateTime is 8 bytes widenot 8 chars. Are you using Enterprise Manager ? If yes then clickon Default value and set the default date to whatever you want in thisformat 'yyyyMMdd' . For example for today '20050606'

Add domain user?

Hi,
By default, when add a domain user in the Domain Controller, is it this new
user can access the sql data?
public role only? Thanks...By default, a new AD user will have no access to a specific SQL Server
unless they are added to a windows group that has access to SQL Server or
their domain account is explicitly added as a login.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"js" <js@.someone@.hotmail.com> wrote in message
news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Hi,
> By default, when add a domain user in the Domain Controller, is it this
new
> user can access the sql data?
> public role only? Thanks...
>|||Thanks Jasper.
Another question about this one: if a particular role of which the user is a
member has been denied a specific object permission (such as SELECT), the
user is unable to exercise that permission. The most restrictive permission
(DENY) takes precedence.
What will happen in this case: MYDN\Test is belonging to local administrator
group and System Admin Server Role. And I set "db_denydatawriter" to this
user in Northwind database. Can this user exec the "Updte ." statement in
Northwind's table?
Please advice...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
> By default, a new AD user will have no access to a specific SQL Server
> unless they are added to a windows group that has access to SQL Server or
> their domain account is explicitly added as a login.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> new
>|||SA will overrule. The user will be SA.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
> Thanks Jasper.
> Another question about this one: if a particular role of which the user is
> a
> member has been denied a specific object permission (such as SELECT), the
> user is unable to exercise that permission. The most restrictive
> permission
> (DENY) takes precedence.
> What will happen in this case: MYDN\Test is belonging to local
> administrator
> group and System Admin Server Role. And I set "db_denydatawriter" to this
> user in Northwind database. Can this user exec the "Updte ." statement in
> Northwind's table?
>
> Please advice...
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
>|||Thanks Jeff.
How to assign denywrite to MYDN\Test have without by removing it from local
administrator?
If I take out the db_owner from BUILTIN\Administrators will cause any
problem'
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> SA will overrule. The user will be SA.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "js" <js@.someone@.hotmail.com> wrote in message
> news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
>|||Go through it first with a fine tooth comb. Make sure the ID that is
running SQL has access by itself and not in a group. Make sure that the
proper Local Admins have direct access accounts and then remove it. Make
yourself a standard login first just for safe keeping if you are in mixed
mode. You should be fine.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:O86FNw5MEHA.3472@.TK2MSFTNGP10.phx.gbl...
> Thanks Jeff.
> How to assign denywrite to MYDN\Test have without by removing it from
> local
> administrator?
> If I take out the db_owner from BUILTIN\Administrators will cause any
> problem'
>
>
> "Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
> news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> is
> the
> this
> in
> or
>

Add a default value to an existing column

How can I add a default value to an existing column via T-SQL? I tried the
following but not successful:
Alter Table MyTbl Alter Column MyCol Int Default 0.8
TIANote that it will not update previously stored null values
Madhivanan|||The statement fails even if the table is empty (no record). I get the error
message:
"Incorrect syntax near the keyword 'Default'."
The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default 0.8
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
> Note that it will not update previously stored null values
> Madhivanan
>|||Hi
create table #test
(
col decimal(18,3)
)
alter table #test add constraint my_def default 18.3 for col
"krygim" <krygim@.hotmail.com> wrote in message
news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> The statement fails even if the table is empty (no record). I get the
error
> message:
> "Incorrect syntax near the keyword 'Default'."
> The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default
0.8
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
>|||It works. Thanks Uri.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kQoOytKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi
> create table #test
> (
> col decimal(18,3)
> )
> alter table #test add constraint my_def default 18.3 for col
>
>
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> error
> 0.8
>

Add a Default to an existing Column

I tried this:
CREATE DEFAULT TempToPerm_DF AS false
sp_bindefault TempToPerm_DF, 'Template.TempToPerm'
and I get this:
The name 'false' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
I reread the documentation and I'm just not getting it.
Any help?
Thanks,
Darwin FiskWhat is the datatype of 'Template.TempToPerm'? If it is bit, then use 0 -
not false. If it is varchar, then use 'false' (quotes included).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Darwin Fisk" <Darwin@.DotNetDesigns.com> wrote in message
news:%238Tt%23SMuFHA.1264@.TK2MSFTNGP12.phx.gbl...
I tried this:
CREATE DEFAULT TempToPerm_DF AS false
sp_bindefault TempToPerm_DF, 'Template.TempToPerm'
and I get this:
The name 'false' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
I reread the documentation and I'm just not getting it.
Any help?
Thanks,
Darwin Fisk|||Duh! bit... 0 not false... Thanks!
Darwin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uUxsuWMuFHA.3188@.TK2MSFTNGP14.phx.gbl...
> What is the datatype of 'Template.TempToPerm'? If it is bit, then use 0 -
> not false. If it is varchar, then use 'false' (quotes included).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Darwin Fisk" <Darwin@.DotNetDesigns.com> wrote in message
> news:%238Tt%23SMuFHA.1264@.TK2MSFTNGP12.phx.gbl...
> I tried this:
> CREATE DEFAULT TempToPerm_DF AS false
> sp_bindefault TempToPerm_DF, 'Template.TempToPerm'
> and I get this:
> The name 'false' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> I reread the documentation and I'm just not getting it.
> Any help?
> Thanks,
> Darwin Fisk
>

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A to
have same value as column B, how can I do that? Thanks.ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
--
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A to
have same value as column B, how can I do that? Thanks.
ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.
|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.

add a column default value from other column

Hi,
I need to add a column A for my table, this column A is the same type as
another one column B. For the exsiting rows I would like this new column A t
o
have same value as column B, how can I do that? Thanks.ALTER TABLE YourTable
ADD COLUMN ColumnA DATATYPE_HERE NULL
UPDATE YourTable
SET ColumnA = ColumnB
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.|||There are serveral ways you can accomplish this.
You can create your new column on the table, and then update it to be the
same as ColumnB
UPDATE tablename
set columnA = ColumnB
Simon Worth
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0676BB0D-ACAF-4AF7-A698-335F84928371@.microsoft.com...
> Hi,
> I need to add a column A for my table, this column A is the same type as
> another one column B. For the exsiting rows I would like this new column A
to
> have same value as column B, how can I do that? Thanks.