Copy a row from a table and insert a new value in SQL-SERVER

Question

I have a problem with this query, I want to copy some columns and add a new value into the same table but doesn't seem to work right. I can't figure out what is wrong

GO
IF OBJECT_ID('dbo.spInsertCopyDocumentos') IS NOT NULL
DROP PROCEDURE spInsertCopyDocumentos
GO
CREATE PROCEDURE spInsertCopyDocumentos
    @IdArtigo int,
    @IdNovo int
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO hDocumentos (IdArtigo) VALUES (@IdNovo)
    SELECT TipoDocumento, NomeDocumento,Dados, Extensao, Observacoes 
    FROM hDocumentos
    WHERE IdArtigo = @IdArtigo
END

It says the TipoDocumento is null but it has values and is an integer... I'm not sure why it says null because if i run the select query it shows the values. Also it has 2 rows, so i might not be sure if this only works for one

EDIT.

I'm copying files from a different record ID hence why i Need @IdNovo here to insert the new value in the foreign key ID column

You don't need VALUES here as it looks like you want SELECT @IdNovo — Ilyes
– Ilyes, Commented Jan 11, 2019 at 14:14
i need to insert the new ID. as the procedure name says, I'm copying files from a different ID — Jackal
– Jackal, Commented Jan 11, 2019 at 14:15
From what I'm seeing, calling the stored procedure will insert @IdNovo to hDocumentos.IdArtigo and then will return the rows from the SELECT below the INSERT. — fdkgfosfskjdlsjdlkfsf
– fdkgfosfskjdlsjdlkfsf, Commented Jan 11, 2019 at 14:27
So calling it will return (1 row(s) affected) and then the results of the SELECT statement. The INSERT and the SELECT and completely separate. — fdkgfosfskjdlsjdlkfsf
– fdkgfosfskjdlsjdlkfsf, Commented Jan 11, 2019 at 14:29

fdkgfosfskjdlsjdlkfsf · Accepted Answer · 2019-01-11 14:32:54Z

2

I'm not sure what hDocumentos looks like, but if you want to insert @IdNovo and the rows from the select it would look something like this:

ALTER PROCEDURE spInsertCopyDocumentos
    @IdArtigo int,
    @IdNovo int
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO hDocumentos (col1, col2, col3, col4, col5, col6)
    SELECT TipoDocumento, NomeDocumento,Dados, Extensao, Observacoes, @IdNovo
    FROM hDocumentos
    WHERE IdArtigo = @IdArtigo
END

answered Jan 11, 2019 at 14:32

fdkgfosfskjdlsjdlkfsf

3,3464 gold badges51 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ilyes · Accepted Answer · 2019-01-11 14:43:24Z

Are you just looking for

IF OBJECT_ID('dbo.spInsertCopyDocumentos') IS NOT NULL
DROP PROCEDURE spInsertCopyDocumentos
GO
CREATE PROCEDURE spInsertCopyDocumentos
    @IdArtigo int,
    @IdNovo int
AS
BEGIN
    SET NOCOUNT ON
    -- You can check the params for NULL here and raise an error
    INSERT INTO hDocumentos (IdArtigo) 
    SELECT @IdNovo 
    FROM hDocumentos
    WHERE IdArtigo = @IdArtigo
END

If you want to copy all the data then you need to specify the columns names as

INSERT INTO hDocumentos (IdArtigo, Col1, Col2, ..)
SELECT @IdNovo, ISNULL(Col1, <DefaultValue>), ISNULL(Col2, <DefaultValue>) ...
FROM hDocumentos
WHERE IdArtigo = @IdArtigo

and use ISNULL() to check for NULLs and replace it with a default value instead of NULL since your column IS NOT NULL.

For the error you get, that because you just specify (IdArtigo) column, that mean you will insert a row have NULLs except for IdArtigo column which you specify in the INSERT clause. So you are trying to insert a NULL value to TipoDocumento column, cause you did not specify which value to insert it to it.

Finally, your procedure should looks like:

IF OBJECT_ID('dbo.spInsertCopyDocumentos') IS NOT NULL
DROP PROCEDURE spInsertCopyDocumentos
GO
CREATE PROCEDURE spInsertCopyDocumentos
    @IdArtigo int,
    @IdNovo int
AS
BEGIN
    SET NOCOUNT ON
    -- Optional if you need to check the paramaters if they NULL or not, that's up to you
    INSERT INTO hDocumentos (IdArtigo, 
                             TipoDocumento, 
                             NomeDocumento, 
                             Dados, 
                             Extensao, 
                             Observacoes) --The number of columns specified her must be the same in the select
    SELECT @IdNovo,
           ISNULL(TipoDocumento, <DefaultValue>), -- since it won't accept nulls
           --But since you are selecting from the same table it won't be a problem 
           NomeDocumento,
           Dados, 
           Extensao, 
           Observacoes 
    FROM hDocumentos
    WHERE IdArtigo = @IdArtigo
END

it still says cannot insert cause this column does not allow nulls, i don't get it if there is no null values

Jackal · Accepted Answer · 2019-01-11 14:30:09Z

1

ok i see how it works. I had to specify the column names on the insert like a normal insert into query

INSERT INTO hDocumentos (TipoDocumento, NomeDocumento, Dados, Extensao, Observacoes,IdArtigo) 
SELECT TipoDocumento, NomeDocumento, Dados, Extensao, Observacoes, @IdNovo
FROM hDocumentos
WHERE IdArtigo = @IdArtigo

answered Jan 11, 2019 at 14:30

Jackal

3,5315 gold badges45 silver badges96 bronze badges

Collectives™ on Stack Overflow

Copy a row from a table and insert a new value in SQL-SERVER

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related