CODEWITHSUNDEEP
sql-serversql-server-2008
Patthebug
Patthebug

Reputation: 4797

Error inserting comma separated values in Table: SQL Server 2008

I have two tables called 'ticket' and 'ticket_category'. 'Ticket' table has a column 'Ticket_Uid' and its type is 'UniqueIdentifier'. Each 'ticket_uid' in 'ticket' table has one-to-many mappings in 'ticket_category' table. E.g.

'Ticket' table:

Ticket_Uid
100

Ticket_Category:

Ticket_Uid    Ticket_Category_Uid
100            ABC
100            DEF
100            XYZ

I want to create the following table named 'comment_mining':

Ticket_Uid    Category_List
100           ABC,DEF,XYZ

The table has already been created using the following:

create table dbo.comment_mining
(
Ticket_UID [uniqueidentifier] NOT NULL,
time_created datetime,
time_closed datetime,
total_time_taken int,
category_list nvarchar(500),
comment_list varchar(8000)
);

I have already created this table and populated the 'Ticket_Uid' column.

For inserting into the 'category_list' column, I am using the following query:

insert into dbo.comment_mining(category_list)
SELECT 
  (SELECT distinct convert(varchar(500),category_id) + ',' AS [text()] 
   FROM ticket_category pr 
   WHERE pr.ticket_uid = p.ticket_uid 
   FOR XML PATH ('')) 
AS Category_list 
FROM comment_mining p

When I run the above query, it gives me the following error:

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

(which is strange as I am not even inserting in the 'Ticket_Uid' column)

When I run the same query without the insert statement, it executes perfectly. The query is as follows:

SELECT 
  (SELECT distinct convert(varchar(500),category_id) + ',' AS [text()] 
   FROM ticket_category pr 
   WHERE pr.ticket_uid = p.ticket_uid 
   FOR XML PATH ('')) 
AS Category_list 
FROM comment_mining p

Yes there are some NULL values when the above query is run, but 'category_list' column in 'comment_mining' table can take NULL values. Why is the error on 'ticket_Uid' column?

Would someone please be able to explain why this is happening and what's the cure to this?

P.S. - I am new to SQL.

Upvotes: 2

Views: 991

Answers (2)

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

The reason you have the insert error on table comment_mining is because you set the Ticket_Uid column as not null; however, since it does not have a default value, the insert fails because whether you're inserting that field specifically or not, when a row is created, all columns must be filled in or be null.

You can do one of 2 things:

  • Change the structure of the comment_mining table to have a default value for Ticket_Uid (You can do this in the table designer or with code:

Example 1:

Alter Table comment_mining 
    Add Constraint DF_CommentMining_1 default NewID() for Ticket_UID
  • Make your insert explicitly include a generated uniqueidentifier (GUID) value by using the SQL NewID() function to populate the Ticket_UID UniqueIdentifier column

Example 2:

insert into dbo.comment_mining(Ticket_Uid, category_list)
SELECT NewID(), 
   [ your subquery ]...

In both cases, you're now satisfying the NOT NULL constraint on comment_mining.Ticket_UID, either by making it automatically populate itself, or by supplying a value.

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

try this,

;with cte as
(    
select 100 Ticket_Uid,'ABC' Ticket_Category_Uid union all
select 100 , 'DEF' union all
select 100, 'XYZ' 
) 

select distinct b.Ticket_Uid,
stuff((select ','+a.Ticket_Category_Uid from cte a where a.Ticket_Uid=b.Ticket_Uid for xml path('')),1,1,'')

from cte b

Upvotes: 0

Related Questions