October 19, 2007 at 2:57 pm
Hey everyone, I am having some trouble here with an INSERT statement. I know it has to be something simple I am overlooking, or by brain is just on vacation today. I am trying to insert on a column that does not allow NULLS, the default value for this column is 'n/a'.
Here is my table:
[font="Courier New"]CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),
[Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),
[Col3] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown')
)[/font]
Here is my insert statement:
[font="Courier New"]INSERT INTO [dbo].[TableA]
([Col1],
[Col2],
[Col3])
SELECT
[Col1],
[Col2],
[Col3]
FROM [dbo].[TableB];[/font]
Note: Column 2 of Table B is NULL.
This is the error message:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Col2', table 'MyDb.dbo.TableA'; column does not allow nulls. INSERT fails.
I am prepared to be embarrassed here. What I am doing wrong? Thanks in advanced.
Regards,
Jon
October 20, 2007 at 6:24 pm
[Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col1] DEFAULT ('unknown'),
You're trying to insert NULLS into a column that does not allow NULLS
You could add:
WHERE TableB.Col2 is not null
same for Col1 & Col3
October 22, 2007 at 6:36 am
Or you could use COALESCE
INSERT INTO [dbo].[TableA]
([Col1],
[Col2],
[Col3])
SELECT
COALESCE([Col1],'unknown'),
COALESCE([Col2],'unknown'),
COALESCE([Col3],'unkown'
FROM [dbo].[TableB];
If you're using the VALUES statement on insert, you can use the default value:
VALUES
(DEFAULT,
'Something',
'Something)
But if you really wanted the defaults to kick in, you need to not refer to the columns at all:
INSERT INTO [dbo].[TableA]
([Col1])
SELECT
[Col1]
FROM [dbo].[TableB];
But if you refer to the columns directly, you need to deal with the values going in.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 23, 2007 at 10:17 pm
Dear whats you are doing you are inserting null values from table b to tableA col1 which is not allow you to insert null values if you want to insert default you should leave the column while inserting data in it.
or make a trigger before creating a trigger you should change null property of col1 table1.It is not posible that you explicit inserting a values to the column and it use default for it.
**if you are trying to insert default value in this table it whould be throw error becouse col1 accept only 5 charector where default value is more than 5('unknown')
change the structure of table too.
A.
INSERT INTO [dbo].[TableA]
([Col2],
[Col3])
SELECT
[Col2],
[Col3]
FROM [dbo].[TableB];
for this solution remove null propery from tableA col1 and use this trigger.
B.
CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [nvarchar](7) CONSTRAINT [DF_TableA_Col111] DEFAULT ('unknown'),
[Col2] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col222] DEFAULT ('unknown'),
[Col3] [nvarchar](5) NOT NULL CONSTRAINT [DF_TableA_Col333] DEFAULT ('unknown')
)
create trigger trginserttableA
on tableA
after insert
as
begin
declare @data varchar(7),@id int
select @data=col1,@id=id from inserted
select * from inserted
if(@data is null)
begin
update tablea
set col1='Unknown'
where id=@id
end
end
INSERT INTO [dbo].[TableA1]
([Col1],
[Col2],
[Col3])
SELECT
[Col1],
[Col2],
[Col3]
FROM [dbo].[TableB];
check the result:----
select * from tablea1
B is the propre solution for you where you can use original value from tableB and unknown for record which is null.
October 24, 2007 at 12:12 pm
Thank you all. I finally got some sleep after a couple of all-nighters. It's amazing how you over look rather simple things with a lack of sleep. Your suggestions worked and got me back on track. Thanks again - Jon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply