1

I am using below stored procedure to insert records from another view:

ALTER PROCEDURE [dbo].[SPemployeeinsert]
    (@date DATETIME)
AS 
BEGIN 
    DECLARE @idinsert AS INT 

    SELECT @idinsert = MAX (ID) + 1 
    FROM dbo.EMP

    INSERT INTO [SRV-RVS].dbo.emp (LASTNAME, ID)
        SELECT 
            [FirstName], 
            @idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
        FROM 
            drv-rds2014.[HumanResources].[testEmployeeView]
        WHERE 
            ModifiedDate = @date 

    INSERT INTO [SRV-RVS].dbo.empldf(CIVILID, JOBTITLE, ISSUEDATE, ID)
        SELECT
            [PhoneNumber], [JobTitle], [ModifiedDate], 
            @idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
        FROM 
            drv-rds2014.[HumanResources].[testEmployeeView]
        WHERE  
            ModifiedDate = @date
END

While executing the stored procedure I am getting this error:

Msg 515, Level 16, State 2, Procedure SPemployeeinsert, Line 42
Cannot insert the value NULL into column 'ID', table 'SRV-RVS.dbo.EMP'; column does not allow nulls. INSERT fails.

Msg 515, Level 16, State 2, Procedure SPemployeeinsert, Line 48
Cannot insert the value NULL into column 'ID', table 'SRV-RVS.dbo.EMPLDF'; column does not allow nulls. INSERT fails.

I am trying to pass date like '01/04/2009;' which will copy from the source and insert into the destination using this stored procedure.

Regards

Screenshot

Stored procedure

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aryan
  • 49
  • 6
  • the message is pretty clear: `Cannot insert the value NULL into column 'ID'`. – Jens Aug 18 '17 at 08:57
  • Why in your first query you use where ID= @idinsert ? – etsa Aug 18 '17 at 08:59
  • @jens . my declare statement needs to insert the maximum value from the ID column and use for the insert statement ..here its returning the error – aryan Aug 18 '17 at 09:38
  • @etsa . I am trying to create variable which will store the ID and pass it two insert statement – aryan Aug 18 '17 at 09:42
  • As Jens said in his answer I think too that WHERE ID=@idinsert seems not correct – etsa Aug 18 '17 at 10:07
  • @etsa ...Now stored procedure is returing duplicate value error . – aryan Aug 18 '17 at 10:10

3 Answers3

1

There are some problems with your procedure. First:

DECLARE @idinsert as int
select @idinsert= MAX (ID)+1 from dbo.EMP
where ID= @idinsert

Variable @idinsert is not initialized, so its value is NULL. You need to change this to:

DECLARE @idinsert as int
select @idinsert= MAX(ID)+1 from dbo.EMP

Second problem - for @date it is possible, that your view drv-rds2014. [HumanResources].[testEmployeeView] will return multiple values and insert queries will fail cause of duplicate values in column Id. You need to change your insert statements to:

INSERT [SRV-RVS].dbo.emp (LASTNAME,ID)
  SELECT [FirstName],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
  FROM drv-rds2014. [HumanResources].[testEmployeeView]
  WHERE ModifiedDate=@date 

  insert [SRV-RVS].dbo.empldf
  (CIVILID,JOBTITLE,ISSUEDATE,ID)
  select [PhoneNumber],[JobTitle],[ModifiedDate],@idinsert + ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1
  FROM drv-rds2014. [HumanResources].[testEmployeeView]
  WHERE  ModifiedDate=@date
Rokuto
  • 814
  • 1
  • 11
  • 16
  • @aryan In comments above you wrote that you have problem `Conversion failed when converting the varchar value 'select (convert(int),MAX (ID)+1 from dbo.EMP)' to data type int.`, but I can't see `select (convert(int),MAX (ID)+1 from dbo.EMP)` in your code... I can't help you if you don't write your ACTUAL code... – Rokuto Aug 18 '17 at 12:29
  • Conversion failed when converting the varchar value 'MAX (ID)+1 from dbo.EMP)' to data type int. , this is the error i am returing after the changes you told me to make – aryan Aug 18 '17 at 12:32
  • `Conversion failed when converting the varchar value 'select (convert(int),MAX (ID)+1 from dbo.EMP)' to data type int.` , this happend when i added a select statment infront of declare statement. sorry for the confusion – aryan Aug 18 '17 at 12:33
  • @aryan I didn't write anywhere `'select (convert(int),MAX (ID)+1 from dbo.EMP)'`, so it is your own code. – Rokuto Aug 18 '17 at 12:34
  • `set@idinsert= select MAX(ID)+1 from dbo.EMP ` . i tried this. thats how i returned the above error. sorry for the confusion there – aryan Aug 18 '17 at 12:38
  • @aryan If you want to use `SET`, you need to write `set @idinsert= (select MAX(ID)+1 from dbo.EMP)`, but it is still not my code... – Rokuto Aug 18 '17 at 12:39
  • i tried your code but its returing `.` ` Conversion failed when converting the varchar value 'MAX (ID)+1 from dbo.EMP)' to data type int` – aryan Aug 18 '17 at 12:43
  • @aryan I am sorry, but I can't believe it... Could you add screenshot with your query? – Rokuto Aug 18 '17 at 12:49
  • @aryan I want to see you procedure, not how you are executing this. – Rokuto Aug 18 '17 at 13:07
  • @aryan Mate, Remove apostrophies and last `)`. It shoould be `SELECT @idinsert = MAX(Id) + 1 FROM dbo.EMP` BUT WITHOUT ANY APOSTROPHIES! – Rokuto Aug 18 '17 at 14:28
  • its fine now .. I can update the table now.. thanks Rokuto for staying this long – aryan Aug 18 '17 at 14:39
  • also, i didint get this part of your change.. `+ ROW_NUMBER() OVER (ORDER BY [FirstName]) - 1` – aryan Aug 18 '17 at 14:41
  • @aryan I am on phone and I can't link you site, but you can Google function Row_Number and read about it. This function returns position of each row in specific order (in this query rows are ordered by first name), -1 is needed to not create gaps on Id column. – Rokuto Aug 18 '17 at 16:17
  • i am familiar with row numbers and ranking functions, but this use-case is something i couldn't get it .. – aryan Aug 18 '17 at 16:21
  • @aryan Hey, here: http://rextester.com/TYPT21417 is simple query with using `ROW_NUMBER` which should help you to understand why used it in your procedure. – Rokuto Aug 18 '17 at 20:55
  • so,it's actually subtracting from the row number directly . could you tell something about `to not to create gaps on ID column`. Even I can insert without that part but what this gap really mean here . – aryan Aug 19 '17 at 07:43
  • @aryan. If you don't substract 1 from `ROW_NUMBER` you will get gaps in Id column, it means that there will be row with id, e.g. 100, but not with 101, because `@idinsert` will be equal 101 (you are adding 1 to maximal id in table `dbo.EMP`), and `ROW_NUMBER` will be always greater than 1. – Rokuto Aug 19 '17 at 11:44
  • i read your post around five times to understood what it really meant .. – aryan Aug 19 '17 at 18:52
0
select @idinsert= MAX (ID)+1 from dbo.EMP
    where ID= @idinsert

Returns null. remove the where clause

Jens
  • 67,715
  • 15
  • 98
  • 113
  • @aryan Remove whole `where ID= @idinsert `. – Rokuto Aug 18 '17 at 09:44
  • Something else is going now, I am getting duplicate value errror , its not incrementing the ID from the column. only trying to use the last ID and using for the insert – aryan Aug 18 '17 at 09:53
  • @aryan You are inserting into 2 different tables. Are you sure, that maximum Id in table `[SRV-RVS].dbo.empldf` is equal to maximum Id in table `[SRV-RVS].dbo.emp`? Which table gives you duplicate value error? – Rokuto Aug 18 '17 at 09:59
  • yes , last ID is same for both tables. I tried to set a fixed value to idinsert and tried , like DECLARE @idinsert as int set @idinsert=194908 , but it return the same error – aryan Aug 18 '17 at 10:08
  • @aryan Database is not a version of your DBMS. :) I want to know, to which database you are actually connected. Try `select MAX (ID) from [SRV-RVS].dbo.EMP` and tell us what is the output. – Rokuto Aug 18 '17 at 10:17
  • @Rokuto its returing the output 194907 – aryan Aug 18 '17 at 10:56
  • @aryan Ok, so what is returning query `SELECT COUNT(*) FROM drv-rds2014.[HumanResources].[testEmployeeView] WHERE ModifiedDate = '01/04/2009'`? – Rokuto Aug 18 '17 at 10:59
  • three records were inserting for the query . – aryan Aug 18 '17 at 11:03
  • @aryan And this is a problem. You are trying to insert three records with the same Id... – Rokuto Aug 18 '17 at 11:04
  • @Rokuto But that is not the Problem, as you can see in the error message `Cannot insert the value NULL ` – Jens Aug 18 '17 at 11:06
  • @Jens It is, he changed query (removed `where ID= @idinsert` as you suggested) and he gets another problem with duplicate values. – Rokuto Aug 18 '17 at 11:06
  • @rokuto .. If, how can i increment ID for each record – aryan Aug 18 '17 at 11:08
  • @aryan Use `SELECT your_columns, @idinsert + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM ...` or do it as Gagan suggested. – Rokuto Aug 18 '17 at 11:10
  • Identity column is not possible for this particular table @Rokuto – aryan Aug 18 '17 at 11:30
  • @aryan so use `SELECT your_columns, @idinsert + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM ...` ... If you need order replace `ORDER BY (SELECT NULL)` with `ORDER BY [FirstName]`. – Rokuto Aug 18 '17 at 11:32
  • Conversion f.ailed when converting the varchar value 'MAX (ID)+1 from dbo.EMP' to data type int .. even changing the datatype doesnt work for me @Rokuto – aryan Aug 18 '17 at 11:47
0

This error comes when you try to insert null data on the columns where null value is not allowed. In your query id can not be null when your inserting data in both tables. You can insert ID or make id as identity.

Gagan Sharma
  • 220
  • 1
  • 7