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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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