p2p.wrox.com: Archives - sql

sql_language thread: sql server id generation
Message #1 by Harish Babu.N <harish_babu@c...> on 22 Nov 2000 13:17:34 +0000
hi 

 but i want to auto generate number for filed of type int.when iuse newid
() for column of datatype int .it gives an error

>> Operand type clash: uniqueidentifier is incompatible with int

how do i over come this problem without changing column data type


Thanks
-Harish







On Thu, 23 November 2000, "jigs gandhi" wrote:

> 
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
> <META content="MSHTML 5.00.2314.1000" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=#ffffff>
> <DIV><FONT color=#800000 face="Comic Sans MS" size=2>hi</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=#800000 face="Comic Sans MS" size=2>the t-sql function NEWID() 
> is what you are looking for.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=#800000 face="Comic Sans MS" size=2>jigs</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=#800000 face="Comic Sans MS" size=2><STRONG>This is what SQL 
> Server 7 Books Online have to say</STRONG></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=#800000 face="Comic Sans MS" size=2> </DIV>
> <DIV><A name=_newid></A><STRONG>NEWID (T-SQL)</STRONG></DIV>
> <DIV>Creates a unique value of type <B>uniqueidentifier</B>. </DIV>
> <DIV>Syntax</DIV>
> <DIV class=syntax>NEWID<B>()</B></DIV>
> <DIV>Return Types</DIV>
> <DIV><B>uniqueidentifier</B></DIV>
> <DIV>Examples</DIV>
> <DIV>A. Use the NEWID function with a variable</DIV>
> <DIV>This example uses NEWID to assign a value to a variable declared as the 
> <B>uniqueidentifier</B> data type. The value of the <B>uniqueidentifier</B> data 
> type variable is printed before the value is tested.</DIV>
> <DIV class=ex><CODE>-- Creating a local variable with DECLARE/SET 
> syntax.</CODE></DIV>
> <DIV class=ex><CODE>DECLARE @myid uniqueidentifier</CODE></DIV>
> <DIV class=ex><CODE>SET @myid = NEWID()</CODE></DIV>
> <DIV class=ex><CODE>PRINT 'Value of @myid is: '+ CONVERT(varchar(255), 
> @myid)</CODE></DIV>
> <DIV class=ex><B><I><CODE>  </CODE></I></B></DIV>
> <DIV>Here is the result set:</DIV>
> <DIV class=ex><CODE>Value of @myid is: 
> 6F9619FF-8B86-D011-B42D-00C04FC964FF</CODE></DIV>
> <DIV class=ex><B><I><CODE>  </CODE></I></B></DIV>
> <DIV>
> <HR>
> </DIV>
> <DIV><SPAN class=NTI><B>Note</B> </SPAN>The value returned by NEWID is different 
> for each computer. This number is shown only for illustration.</DIV>
> <DIV>
> <HR>
> </DIV>
> <DIV>B. Use NEWID in a CREATE TABLE statement</DIV>
> <DIV>This example creates <B>cust</B> table with a <B>uniqueidentifier</B> data 
> type, and uses NEWID to fill the table with a default value. In assigning the 
> default value of NEWID(), each new and existing row has a unique value for the 
> <B>cust_id</B> column. </DIV>
> <DIV class=ex><CODE>-- Creating a table using NEWID for uniqueidentifier data 
> type. </CODE></DIV>
> <DIV class=ex><CODE>CREATE TABLE cust</CODE></DIV>
> <DIV class=ex><CODE>(</CODE></DIV>
> <DIV class=ex><CODE> cust_id uniqueidentifier NOT NULL</CODE></DIV>
> <DIV class=ex><CODE>    DEFAULT newid(),</CODE></DIV>
> <DIV class=ex><CODE> company varchar(30) NOT NULL,</CODE></DIV>
> <DIV class=ex><CODE> contact_name varchar(60) NOT NULL, </CODE></DIV>
> <DIV class=ex><CODE> address varchar(30) NOT NULL, </CODE></DIV>
> <DIV class=ex><CODE> city varchar(30) NOT NULL,</CODE></DIV>
> <DIV class=ex><CODE> state_province varchar(10) NULL,</CODE></DIV>
> <DIV class=ex><CODE> postal_code varchar(10) NOT NULL, </CODE></DIV>
> <DIV class=ex><CODE> country varchar(20) NOT NULL, </CODE></DIV>
> <DIV class=ex><CODE> telephone varchar(15) NOT NULL,</CODE></DIV>
> <DIV class=ex><CODE> fax varchar(15) NULL</CODE></DIV>
> <DIV class=ex><CODE>)</CODE></DIV>
> <DIV class=ex><CODE>GO</CODE></DIV>
> <DIV class=ex><CODE>-- Inserting data into cust table.</CODE></DIV>
> <DIV class=ex><CODE>INSERT cust</CODE></DIV>
> <DIV class=ex><CODE>(cust_id, company, contact_name, address, city, 
> state_province, </CODE></DIV>
> <DIV class=ex><CODE> postal_code, country, telephone, fax)</CODE></DIV>
> <DIV class=ex><CODE>VALUES</CODE></DIV>
> <DIV class=ex><CODE>(newid(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 
> 38', 'Oulu', NULL,</CODE></DIV>
> <DIV class=ex><CODE> '90110', 'Finland', '981-443655', 
> '981-443655')</CODE></DIV>
> <DIV class=ex><CODE>INSERT cust</CODE></DIV>
> <DIV class=ex><CODE>(cust_id, company, contact_name, address, city, 
> state_province,</CODE></DIV>
> <DIV class=ex><CODE>postal_code, country, telephone, fax)</CODE></DIV>
> <DIV class=ex><CODE>VALUES </CODE></DIV>
> <DIV class=ex><CODE>(newid(), 'Wellington Importadora', 'Paula Parente', 'Rua do 
> Mercado, 12', 'Resende', 'SP',</CODE></DIV>
> <DIV class=ex><CODE> '08737-363', 'Brazil', '(14) 555-8122', 
> '')</CODE></DIV>
> <DIV class=ex><CODE>INSERT cust</CODE></DIV>
> <DIV class=ex><CODE>(cust_id, company, contact_name, address, city, 
> state_province,</CODE></DIV>
> <DIV class=ex><CODE> postal_code, country, telephone, fax)</CODE></DIV>
> <DIV class=ex><CODE>VALUES</CODE></DIV>
> <DIV class=ex><CODE>(newid(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 
> 'Cerrito 333', 'Buenos Aires', NULL, </CODE></DIV>
> <DIV class=ex><CODE> '1010', 'Argentina', '(1) 135-5555', '(1) 
> 135-4892')</CODE></DIV>
> <DIV class=ex><CODE>INSERT cust</CODE></DIV>
> <DIV class=ex><CODE>(cust_id, company, contact_name, address, city, 
> state_province,</CODE></DIV>
> <DIV class=ex><CODE> postal_code, country, telephone, fax)</CODE></DIV>
> <DIV class=ex><CODE>VALUES </CODE></DIV>
> <DIV class=ex><CODE>(newid(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 
> 'Graz', NULL,</CODE></DIV>
> <DIV class=ex><CODE> '8010', 'Austria', '7675-3425', 
> '7675-3426')</CODE></DIV>
> <DIV class=ex><CODE>INSERT cust</CODE></DIV>
> <DIV class=ex><CODE>(cust_id, company, contact_name, address, city, 
> state_province,</CODE></DIV>
> <DIV class=ex><CODE> postal_code, country, telephone, fax)</CODE></DIV>
> <DIV class=ex><CODE>VALUES </CODE></DIV>
> <DIV class=ex><CODE>(newid(), 'Maison Dewey', 'Catherine Dewey', 'Rue 
> Joseph-Bens 532', 'Bruxelles', NULL,</CODE></DIV>
> <DIV class=ex><CODE> 'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 
> 68')</CODE></DIV>
> <DIV class=ex><CODE>GO</CODE></DIV>
> <DIV class=ex><B><I><CODE>  </CODE></I></B></DIV>
> <DIV>C. Use uniqueidentifier and variable assignment</DIV>
> <DIV>This example declares a local variable called <B>@myid</B> as a variable of 
> <B>uniqueidentifier</B> data type. Then, the variable is assigned a value using 
> the SET statement.</DIV>
> <DIV class=ex><CODE>DECLARE @myid uniqueidentifier </CODE></DIV>
> <DIV class=ex><CODE>SET @myid = 
> 'A972C577-DFB0-064E-1189-0154C99310DAAC12'</CODE></DIV>
> <DIV class=ex><CODE>GO</CODE></DIV>
> <DIV class=ex><CODE></CODE> </DIV>
> <DIV class=ex><CODE></CODE> </DIV>
> <P class=ex><B><I><CODE>  </CODE></I></B></FONT>----- Original
Message 
> ----- </P>
> <BLOCKQUOTE 
> style="BORDER-LEFT: #800000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
>   <DIV 
>   style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> 
>   <A href="mailto:harish_babu@c..." 
>   title=harish_babu@c...>Harish Babu.N</A> </DIV>
>   <DIV style="FONT: 10pt arial"><B>To:</B> <A 
>   href="mailto:sql_language@p..." title=sql_language@p...>sql 
>   language</A> </DIV>
>   <DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, November 22, 2000 6:47 
>   PM</DIV>
>   <DIV style="FONT: 10pt arial"><B>Subject:</B> [sql_language] sql server id 
>   generation</DIV>
>   <DIV><BR></DIV>hi all <BR> Iam using Sql server 7.0 <BR>  I want to 
>   auto generate the id in the sql script.Iam not setting identity in the 
>   <BR>design mode i want to auto generate id through 'Sql Script Only'.how do i 
>   do this <BR><BR> <BR>thanks in advance <BR>   
>   <BR><BR><BR>Chequemail.com - a free web based e-mail service that also 
>   pays!!!<BR><A 
>   href="http://www.chequemail.com">http://www.chequemail.com</A><BR><BR>---<BR>Wrox 
>   Professional Web Developer Conference II<BR><A 
>   href="http://www.wroxconferences.com/WebDevEurope">http://www.wroxconferences.com/WebDevEurope</A><BR>November
>   29th - December 1st 2000, Amsterdam, Netherlands<BR>Create powerful 
>   distributed web applications serving you now and<BR>in the future. From ASP+ 
>   and VB.NET to XML and SQL Server 2000<BR>---<BR>You are currently subscribed 
>   to sql_language as: <A 
>   href="mailto:newsgroup@h...">newsgroup@h...</A><BR>To unsubscribe 
>   send a blank email to <A 
>   href="mailto:$subst('Email.Unsub')">$subst('Email.Unsub')</A><BR><BR></BLOCKQUOTE>
Return to Index
P2P Archives
