Change column with Uniqueidentifier and newid() | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Change column with Uniqueidentifier and newid()

I’m success add new column in mytable: ALTER TABLE mytable
ADD recordate smalldatetime NULL
CONSTRAINT recordate
DEFAULT getdate() WITH VALUES My Question : How to change old column to uniqueidentifier and ‘newid()’ for default values with command SQL. e.g: old column :
‘OrderID’ uniqueidentifier is NULL Alter Update column:
‘OrderID’ uniqueidentifier is NULL and newid() default with value
[Post contents removed for reasons of stupidity.]
You need to drop that column and add it with default value Alter table mytable
Drop column OrderID GO ALTER TABLE mytable
ADD OrderId uniqueIdentifier default newid()
Madhivanan Failing to plan is Planning to fail
how about alter table xxx add constraint def_orderid default newid() for OrderID with values Cheers
Twan
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Looks like you’re new to Microsoft SQL Server, or you’re looking for support on a different database platform. Anyway, that doesn’t look like valid T-SQL to me.<br /><br />There are usually a couple of posters hanging out here who know their way around Oracle and MySQL, so you may be in luck.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Okay, so I’ve just shot myself in the foot [:0][V][V] … ouch!
/* alter table MyTable add constraint def_orderid default newid() for OrderID with values */<br /><br />Result:<br />The command(s) completed successfully. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Thank’s for Advance<br />
Don’t worry Adriaan,<br /><br />it happens to the best of us <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Twan
How to convert from DTS (EXCEL to MsSQL) and change column DataType or Default Value e.g :
excel ‘Sheet’: "received" > MsSQL ‘MyColumn’:"1"
excel ‘Sheet’: "pending" > MsSQL ‘MyColumn’:"0"
excel ‘Sheet’: "NULL" > MsSQL ‘MyColumn’:"0"
excel ‘Sheet’: "True" > MsSQL ‘MyColumn’:"1"
excel ‘Sheet’: "False" > MsSQL ‘MyColumn’:"0" fyi:
‘mycolumn’ datatype is bit

Case When col=’received’ then 1 else 0 end Madhivanan Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Looks like you’re new to Microsoft SQL Server, or you’re looking for support on a different database platform. Anyway, that doesn’t look like valid T-SQL to me.<br /><br />There are usually a couple of posters hanging out here who know their way around Oracle and MySQL, so you may be in luck.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Okay, so I’ve just shot myself in the foot [:0][V][V] … ouch!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hm, pretty much like the PSV Eindhoven yesterday in Gelsenkirchen. [<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
how type in Query Analyzer ?
" Case When col=’received’ then 1 else 0 end "

Is this question related to your other one or is this a new question? If it’s a new question, you should open up a new thread. That will increase your chances to get a good and quick answer. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Select Case When col=’received’ then 1 else 0 end from yourTable
Madhivanan Failing to plan is Planning to fail
quote:Originally posted by FrankKalis Is this question related to your other one or is this a new question? If it’s a new question, you should open up a new thread. That will increase your chances to get a good and quick answer. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Ok.
]]>