CODEWITHSUNDEEP
sql-server-2008insertidentity-column
GLP
GLP

Reputation: 3675

how to populate date from table1 to table2?

I am trying to populate the records from table1 to table2. table1 has three columns and no primary key. Table2 has three same columns with an identity column as a primary key. Following are my query and definitions. But somehow I got an error, Cannot insert the value NULL into column 'notes_id', table 'table1; column does not allow nulls. INSERT fails. The statement has been terminated. What do I miss?

CREATE TABLE [dbo].[table1](
    [notes_id] [int] NOT NULL,
    [acty_id] [varchar](80) NOT NULL,
    [notes_datetime] [datetime] NOT NULL,
    [notes_data] [nvarchar](max) NULL,
 CONSTRAINT [PK_tblSRMNotes] PRIMARY KEY CLUSTERED 
(
    [notes_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[table2](
    [acty_id] [varchar](80) NOT NULL,
    [notes_datetime] [datetime] NOT NULL,
    [notes_data] [nvarchar](max) NULL
) ON [PRIMARY]

insert into table1(acty_id, notes_datetime, notes_data)
    select s.acty_id, s.notes_datetime, s.notes_data
    from table2 s

Upvotes: 0

Views: 116

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

You've set the first column to NOT NULL, and declared it as the PRIMARY KEY, but you haven't indicated how it should be populated. Typically this is through an IDENTITY column:

CREATE TABLE [dbo].[table1]
(
    [notes_id] [int] IDENTITY(1,1) NOT NULL,
---------------------^^^^^^^^^^^^^
    [acty_id] [varchar](80) NOT NULL,
    [notes_datetime] [datetime] NOT NULL,
    [notes_data] [nvarchar](max) NULL,
    CONSTRAINT [PK_tblSRMNotes] PRIMARY KEY CLUSTERED (notes_id)
);

Upvotes: 2

Related Questions