How to insert data into identity column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to insert data into identity column

Author

Topic

terrence_chan99
Starting Member

12 Posts

Posted - 2006-06-29 : 19:20:55

I have two tables on has identity column, I wonder how can I insert data into the other_new table.

CREATE TABLE [dbo].[Other](
[RecordID] [int] NULL,
[Value] [money] NULL,
[Comment] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Other_New](
[OtherID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NULL,
[Value] [money] NOT NULL,
[Comment] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Other_New] PRIMARY KEY CLUSTERED
(
[OtherID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

This is working fine.

insert into other_new (recordid, value, comment) values (10,50,'Test')

This doesn't work

insert into other_new (recordid, value, comment)
values
(
select
recordid,
value,
comment
from other
)

Error Mesage

Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-29 : 19:27:05

insert into other_new (recordid, value, comment)
select recordid, value, comment
from other

Tara Kizer
aka tduggan

terrence_chan99
Starting Member

12 Posts

Posted - 2006-06-29 : 19:30:49

I got this error.

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Comment', table 'DeceasedEstates.dbo.Other_New'; column does not allow nulls. INSERT fails.
The statement has been terminated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-29 : 19:32:47

Well you've got NULL data in the Other table and Other_New doesn't allow it. So you'll need to either exclude these rows from the select statement, provide values for the null data, or update the data to non-null.

Both value and comment allows nulls in the Other table. In Other_New, they don't.

Tara Kizer
aka tduggan

terrence_chan99
Starting Member

12 Posts

Posted - 2006-06-29 : 19:33:04

After I set the column 'Comment' to nullable, it fixed the issue.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources