column does not allow nulls. INSERT fails

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 2005 Forums

Transact-SQL (2005)

column does not allow nulls. INSERT fails

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-09-11 : 11:46:54

I am migrating data from one DB to another and have this script below which looks at users in the DB. My problem is that when executing the code it errors and says this

Msg 515, Level 16, State 2, Line 24
Cannot insert the value NULL into column 'id', table 'thejanitor.dbo.users'; column does not allow nulls. INSERT fails.
The statement has been terminated.

and also this

Msg 515, Level 16, State 2, Line 77
Cannot insert the value NULL into column 'id', table 'thejanitor.dbo.user_contract'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This is my line24
INSERT INTO thejanitor.dbo.users ([forename], [surname], [displayname],

This is my line77
INSERT INTO thejanitor.dbo.user_contract ([user_id], contract_id)

How can I get around this problem?


USE thejanitor
DECLARE @pID INT
DECLARE @pForename VARCHAR(100)
DECLARE @pSurname VARCHAR(100)
DECLARE @pUserName VARCHAR(100)
DECLARE @pucID INT
DECLARE @pClients VARCHAR(50)
DECLARE Users CURSOR FOR
SELECT DISTINCT id, forename , surname , username
FROM navitas.dbo.users
WHERE Salt IS NOT NULL
AND forename <> 'Deleted'
AND (forename IS NOT NULL
AND surname IS NOT NULL
AND username IS NOT NULL)
AND id NOT IN (SELECT SiRPSID 
FROM MigrationDB.dbo.MigrationLookup
WHERE SiRPSTableName = 'users')
OPEN Users
FETCH NEXT FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.users ([forename], [surname], [displayname],  
[job_title], [department], [username], [password], [salt], [default_contract], 
[active_since], [last_logon], [force_password_change], [active], [visible], 
[show_admin], [reset], [application_version])	
SELECT DISTINCT [forename], [surname], [display_name], [job_title], [department], 
[username], [password], [salt], [default_client], [active_since], [last_logon], 
[force_password_change], [active], [visible], [show_admin], [reset], 
[application_version]
FROM [navitas].[dbo].[users]
WHERE forename = @pForename 
AND surname = @pSurname
AND username = @pUsername
AND salt IS NOT NULL
AND forename <> 'deleted'
AND id = @pID
IF @@ERROR <> 0
BEGIN
INSERT INTO [MigrationDB].[dbo].[ErrorLogging] ([ErrorDetails]) 
VALUES ('error occured when inserting users. ~ SiRPS ID:'+ CONVERT(VARCHAR,@pID))
END
ELSE
BEGIN			
DECLARE @pOID INT
SET @pOID = SCOPE_IDENTITY()
EXECUTE [MigrationDB].[dbo].[AddDataTolookup] @pID,@pOID,'users'
END
DECLARE clients CURSOR FOR
SELECT id,[name] 
FROM navitas.dbo.client 
WHERE [name] 
IN (SELECT [name] 
FROM navitas.dbo.user_client
WHERE [user_id] = @pID)
OPEN clients
FETCH clients INTO @pucID,@pClients
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @pContractID INT
DECLARE @pUserId INT
SELECT DISTINCT @pUserId = id
FROM thejanitor.dbo.users
WHERE forename = @pForename
AND surname = @pSurname
AND username = @pUserName
SELECT DISTINCT @pContractID = id 
FROM thejanitor.dbo.contracts
WHERE [name] = @pClients
IF (@pContractID IS NULL)
BEGIN 
PRINT @pID+ @pForename+@pSurname+@pUserName
END
ELSE
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([user_id], contract_id)
VALUES (@pUserId,@pContractID)
END
FETCH clients INTO @pucID,@pClients
END
CLOSE clients
DEALLOCATE clients
FETCH NEXT 
FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
END
CLOSE Users
DEALLOCATE Users
GO
DECLARE @pMeID INT
DECLARE Client CURSOR FOR 
(SELECT JanitorID 
FROM [MigrationDB].dbo.MigrationLookup
WHERE SiRPSTableName = 'client')
OPEN Client
FETCH NEXT FROM Client 
INTO @pMeID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([user_id],contract_id)
VALUES (94,@pMeID)
FETCH NEXT FROM Client 
INTO @pMeID
END
CLOSE Client
DEALLOCATE Client

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-11 : 11:52:59

this just makes me cry

you can't insert null to columns that doesn't allow nulls
primary key doesn't allow nulls

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-09-11 : 12:28:03

1. if you can avoid using the code


EXECUTE [MigrationDB].[dbo].[AddDataTolookup] @pID,@pOID,'users'

you can avoid the cursor route all together, My assumption is that code simply inserts a record into a table, so there is no need for it.

2. The error is you are not inserting the id. Change the two inserts to


INSERT INTO thejanitor.dbo.users (ID,[forename], [surname], [displayname],
[job_title], [department], [username], [password], [salt], [default_contract], 
[active_since], [last_logon], [force_password_change], [active], [visible], 
[show_admin], [reset], [application_version])
SELECT DISTINCT [forename], [surname], [display_name], [job_title], [department], 
[username], [password], [salt], [default_client], [active_since], [last_logon], 
[force_password_change], [active], [visible], [show_admin], [reset], 
[application_version]
FROM [navitas].[dbo].[users]
WHERE forename = @pForename 
AND surname = @pSurname
AND username = @pUsername
AND salt IS NOT NULL
AND forename <> 'deleted'
AND id = @pID

and


INSERT INTO thejanitor.dbo.user_contract (id,[user_id],contract_id)
VALUES (@pMeID,94,@pMeID)
--This is assuming pmeid is the ID you want.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-09-11 : 18:36:54

Thanks for that Vinnie881, I'll give this a look and see how I get on :-)

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-09-12 : 04:08:28

I have given that a go and now it tells me that I have fewer items in my SELECT statement than I do on the INSERT statement, from adding the ID into the top query (Posted - 09/11/2009 : 12:28:03)

Msg 120, Level 15, State 1, Line 24
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


USE thejanitor
DECLARE @pID INT
DECLARE @pForename VARCHAR(100)
DECLARE @pSurname VARCHAR(100)
DECLARE @pUserName VARCHAR(100)
DECLARE @pucID INT
DECLARE @pClients VARCHAR(50)
DECLARE Users CURSOR FOR
SELECT DISTINCT id, forename , surname , username
FROM navitas.dbo.users
WHERE Salt IS NOT NULL
AND forename <> 'Deleted'
AND (forename IS NOT NULL
AND surname IS NOT NULL
AND username IS NOT NULL)
AND id NOT IN (SELECT SiRPSID 
FROM MigrationDB.dbo.MigrationLookup
WHERE SiRPSTableName = 'users')
OPEN Users
FETCH NEXT FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.users (ID,[forename], [surname], [displayname],
[job_title], [department], [username], [password], [salt], [default_contract], 
[active_since], [last_logon], [force_password_change], [active], [visible], 
[show_admin], [reset], [application_version])
SELECT DISTINCT [forename], [surname], [display_name], [job_title], [department], 
[username], [password], [salt], [default_client], [active_since], [last_logon], 
[force_password_change], [active], [visible], [show_admin], [reset], 
[application_version]
FROM [navitas].[dbo].[users]
WHERE forename = @pForename 
AND surname = @pSurname
AND username = @pUsername
AND salt IS NOT NULL
AND forename <> 'deleted'
AND id = @pID
IF @@ERROR <> 0
BEGIN
INSERT INTO [MigrationDB].[dbo].[ErrorLogging] ([ErrorDetails]) 
VALUES ('error occured when inserting users. ~ SiRPS ID:'+ CONVERT(VARCHAR,@pID))
END
ELSE
DECLARE clients CURSOR FOR
SELECT id,[name] 
FROM navitas.dbo.client 
WHERE [name] 
IN (SELECT [name] 
FROM navitas.dbo.user_client
WHERE [user_id] = @pID)
OPEN clients
FETCH clients INTO @pucID,@pClients
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @pContractID INT
DECLARE @pUserId INT
SELECT DISTINCT @pUserId = id
FROM thejanitor.dbo.users
WHERE forename = @pForename
AND surname = @pSurname
AND username = @pUserName
SELECT DISTINCT @pContractID = id 
FROM thejanitor.dbo.contracts
WHERE [name] = @pClients
IF (@pContractID IS NULL)
BEGIN 
PRINT @pID+ @pForename+@pSurname+@pUserName
END
ELSE
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([user_id], contract_id)
VALUES (@pUserId,@pContractID)
END
FETCH clients INTO @pucID,@pClients
END
CLOSE clients
DEALLOCATE clients
FETCH NEXT 
FROM Users 
INTO @pID,@pForename,@pSurname,@pUserName
END
CLOSE Users
DEALLOCATE Users
GO
DECLARE @pMeID INT
DECLARE Client CURSOR FOR 
(SELECT JanitorID 
FROM [MigrationDB].dbo.MigrationLookup
WHERE SiRPSTableName = 'client')
OPEN Client
FETCH NEXT FROM Client 
INTO @pMeID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO thejanitor.dbo.user_contract ([id], [user_id],contract_id)
VALUES (@pMeID,94,@pMeID)
FETCH NEXT FROM Client 
INTO @pMeID
END
CLOSE Client
DEALLOCATE Client

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:21:45

I really dont think you need cursor here. its better to go for set based soln. if you can post some sample data and reqd o/p, someone will help u out

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-09-13 : 16:05:57

I have tried taking out the Cursor for section, but still get the error message

Msg 120, Level 15, State 1, Line 24
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

I cant post sample data for some reason, it wont let me attach anything.

Basically I am migrating data from 1 SQL Server DB to another SQL Server DB. the table structures are pretty much the same, the odd field names might be slightly different.

Is there a better way to do this or am i doing ok this way?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-14 : 00:16:04

Hi,

Please check the datatype for each column, and also check the datatype for columns in the select list. or
Please check the no of columns in the insert statement and select statement.

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-09-14 : 03:42:47

I have checked them through and they all match apart from displayname in the INSERT Statement has a datatype of VARCHAR(100) but the displayname in the SELECT Statement doesnt have a datatype. That is the only differing thing. The only other thing is that the no of columns dont match as per the reply on Posted - 09/11/2009 : 12:28:03

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 11:06:28

you dont need to attach anything. just post some data here and explain what you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources