Insert output of stored procedure into temp table along with parameter-CodePudding

I want to insert the output of a stored procedure and along parameter value into a temp table.

Issue = If I have 3 records, then am getting 1st id repeating 3 times, 2nd id repeating 2 times and 3rd id repeating 1 time. What would be the issue?

Here is the code I tried.

DECLARE @param INT
DECLARE @tempable table (fname varchar(10), lname varchar(10)), city varchar(10), country 
   varchar(10))
DECLARE @tempable1 table (fname varchar(10), lname varchar(10)), city varchar(10), country varchar(10), id int)

DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
    SELECT distinct empid FROM emp

OPEN curs
FETCH NEXT FROM curs INTO @param

WHILE @@FETCH_STATUS = 0 BEGIN
    insert into @temtable
    exec dbo.employeedata null, null, @param, null, null
    select * from @temptable
    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

insert into @tempable1 (fname, lname, city, country, id)
    select fname, lname, city, country, @param from @temptable

select * from @tempable1

CodePudding user response：

It is an annoying problem when you use INSERT INTO with EXEC and want additional columns. I think the solution here is to move the rows to the new table in the loop, then delete them from the first table to avoid duplicates. See below:

DECLARE @param INT;
DECLARE @tempable TABLE (fname varchar(10), lname varchar(10)), city varchar(10), country varchar(10));
DECLARE @tempable1 TABLE (fname varchar(10), lname varchar(10)), city varchar(10), country varchar(10), id int);

DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
    SELECT distinct empid FROM emp;

OPEN curs;
FETCH NEXT FROM curs INTO @param;

WHILE @@FETCH_STATUS = 0 BEGIN
    INSERT INTO @temtable
    EXEC dbo.employeedata null, null, @param, null, null;

    -- select * from @temptable;

    INSERT INTO @tempable1 (fname, lname, city, country, id)
        SELECT fname, lname, city, country, @param
        FROM @temptable;

    DELETE FROM @temptable;

    FETCH NEXT FROM curs INTO @param;
END;

CLOSE curs;
DEALLOCATE curs;

SELECT * FROM @tempable1;

CodePudding user response：

You can do it in one temp table, perform an update ... where ... is null after insert into ..exec

DECLARE @param INT

DECLARE @tempable1 table 
(
    fname varchar(10), 
    lname varchar(10)), 
    city varchar(10), 
    country varchar(10), 
    id int
)

DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
    SELECT distinct empid FROM emp

OPEN curs
FETCH NEXT FROM curs INTO @param

WHILE @@FETCH_STATUS = 0 BEGIN
    -- change to @temtable1 and specify the column name explicityly
    insert into @temtable1 (fname, lname, city, country)
    exec dbo.employeedata null, null, @param, null, null

    -- update id column with @parm where id is null
    update @temptable1
    set    id = @param
    where  id is null

    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs
    
select * from @tempable1