--------------C21F6E1DF734F73952103489
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Let me try again..... I'm new to MS SQL. Right now I'm just using SQLW
so that I can test out my contraints and triggers. All I want to know
is how do I get the Identity value to insert itself via a INSERT sql
statement? If I use the code below I get this error:
/*-----------------------------
INSERT test (trackid,status) values (default,'status 1')
-----------------------------*/
Msg 515, Level 16, State 3
Attempt to insert the value NULL into column 'trackid', table
'CAMS2.ITJAENIC.test'; column does not allow nulls. INSERT fails.
Command has been aborted.
None of the books I have completely explain how to do this
code sample:
CREATE TABLE ITJAENIC.test (
trackid int IDENTITY (100, 1) PRIMARY KEY NONCLUSTERED ,
status varchar (15) NOT NULL)
SET IDENTITY_INSERT ITJAENIC.test ON
INSERT test (trackid,status) values (DEFAULT,'status 1')
However, this works fine:
create table #temp (col1 int identity(100,1))
select into #temp default values
.... I understand this part..... but what about
create table #temp (col1 int identity(100,1),status varchar(15))
select * from #temp where identitycol=@@identity
now, how do I use an insert statement with an identity and other
datatypes ?
insert into #temp values (@@identity,'status 1') ? ....
Thanks,
Karla
Quote
Scott Samet [TeamB] wrote:
> Don't insert anything into the Identity field. It will be populated
> by
> the server.
--------------C21F6E1DF734F73952103489
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<HTML>
Let me try again..... I'm new to MS SQL. Right now I'm just
using SQLW so that I can test out my contraints and triggers. All
I want to know is how do I get the Identity value to insert itself via
a INSERT sql statement? If I use the code below I get this error:
<P>/*-----------------------------
<BR>INSERT test (trackid,status) values (default,'status 1')
<BR>-----------------------------*/
<BR>Msg 515, Level 16, State 3
<BR>Attempt to insert the value NULL into column 'trackid', table 'CAMS2.ITJAENIC.test';
column does not allow nulls. INSERT fails. Command has been aborted.
<P>None of the books I have completely explain how to do this
<P>code sample:
<BR>CREATE TABLE ITJAENIC.test (
<BR> trackid int IDENTITY (100, 1) PRIMARY KEY
NONCLUSTERED ,
<BR> status varchar (15) NOT NULL)
<P>SET IDENTITY_INSERT ITJAENIC.test ON
<P>INSERT test (trackid,status) values (DEFAULT,'status 1')
<P>However, this works fine:
<P>create table #temp (col1 int identity(100,1))
<BR>select into #temp default values
<P>.... I understand this part..... but what about
<BR>create table #temp (col1 int identity(100,1),status varchar(15))
<BR>select * from #temp where identitycol=@@identity
<P>now, how do I use an insert statement with an identity <B>and</B> other
datatypes ?
<BR>insert into #temp values (@@identity,'status 1') ? ....
<P>Thanks,
<BR> Karla
<BR>
Quote
<P>Scott Samet [TeamB] wrote:
<BLOCKQUOTE TYPE=CITE>Don't insert anything into the Identity field.
It will be populated by
<BR>the server.</BLOCKQUOTE>
</HTML>
--------------C21F6E1DF734F73952103489--
