Inserting data from a SP

[robertfah]

I've got the following temp table and code that populates the temp table just fine:

CREATE TABLE #DistinctProductVersions
(
	VersionNumber varchar(50),
	ProductVersionID int,
	OrderBy int,
	TotalCount int
)

INSERT INTO #DistinctProductVersions
EXEC dbo.ASC_NGSC_GetAllSiteVersionNumbers

The SP beign called in the Insert statement returns the 4 fields to populate the temp table....all is fine. But I've implemented some other code in there that gives me an additional field that I want to add to the temp table. Problem is, the additional field is in a variable, not coming from the SP. So my ? is, can I somehow add this to the Insert statement without it bombing? I've tried several variation of it but can't seem to get it to work. Here's what I've tried:

CREATE TABLE #DistinctProductVersions
(
	VersionNumber varchar(50),
	ProductVersionID int,
	OrderBy int,
	TotalCount int,
	DBName varchar(50)
)

--Added the extra field DBName to the temp table.

INSERT INTO #DistinctProductVersions(VersionNumber, ProductVersionID, OrderBy, TotalCount, DBName)
EXEC dbo.ASC_NGSC_GetAllSiteVersionNumbers @DBName SELECT @DBName

The above code compiles ok, but when ran it says:

(14 row(s) affected)

Server: Msg 213, Level 16, State 7, Line 2
Insert Error: Column name or number of supplied values does not match table definition.

any ideas?

 

[gmmastros]

No.

The temp table's structure must match the output columns from the stored procedure. You could alter the temp table after you load it, like this...

CREATE TABLE #DistinctProductVersions
(
    VersionNumber varchar(50),
    ProductVersionID int,
    OrderBy int,
    TotalCount int
)

INSERT INTO #DistinctProductVersions
EXEC dbo.ASC_NGSC_GetAllSiteVersionNumbers

Alter Table #DistinctProductVersions Add DBName varchar(50)

Update #DistinctProductVersions Set DBName = @DBName

I didn't test this, so there may be minor syntax problems.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

One thing I failed to mention was that this code is inside a loop:

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #DistinctProductVersions
	EXEC dbo.ASC_NGSC_GetAllSiteVersionNumbers @DBName
		
	FETCH NEXT FROM DBNames INTO @DBName
END

So the above code would work the first time through, but fail after that because the # of fields won't match the INSERT statement.

This sounds like I may have to do some real hard thinking "outside" the 'SQL' box.....

 

[gmmastros]

Well... You can't get around the Insert/Exec issue (where the table structure must match).

You could create a table with the structure you want, and another that matches the stored procedure. Then, in the loop, insert/exec from the sp, and then insert into the other table, including the @DBName column.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SajidAttar]

Did you try listing the column names in insert/exec statement, with the fields that procedure returns the values for and later update the additional field with separate update with your variable value..

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #DistinctProductVersions (VersionNumber, ProductVersionID, OrderBy, TotalCount)
    EXEC dbo.ASC_NGSC_GetAllSiteVersionNumbers @DBName

    Update #DistinctProductVersions Set DBName = @DBName
    Where DBName is Null
        
    FETCH NEXT FROM DBNames INTO @DBName
END

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD