INSERT INTO with variable number of columns

why not alter your storedproc to either return Select field1,field2 or select field1,null?

It's not my stored proc, and with be a "big deal" to change it...

All depends on capturing the string output from the sproc into a variable.   If you can do that, this will work as a messy kind of workaround...

create table #onecol (
col1 varchar(50)
)

create table #twocol (
col1 varchar(50),
col2 varchar(50)
)

create table #test (
col1 varchar(50),
col2 varchar(50)
)


insert into #onecol values('A')
insert into #onecol values('B')
insert into #onecol values('C')

insert into #twocol values('A','X')
insert into #twocol values('B','Y')
insert into #twocol values('C','Z')


declare @selcmd varchar(100), @strsearch tinyint

--select @selcmd = 'SELECT Field1, Field2
--FROM MyTable'

select @selcmd = 'SELECT Field1
FROM MyTable'

select @strsearch = CHARINDEX ( ',', @selcmd )

if @strsearch > 0
begin
   insert into #test (col1, col2)
   select * from #twocol

   select * from #twocol
end
else
begin
   if @strsearch = 0
   begin
      insert into #test (col1)
      select * from #onecol

      select * from #onecol
   end
end


drop table #onecol
drop table #twocol
drop table #test

yeh, just put this at the top of what I suggested above.   Sometimes 'ugly' is the only option we have.

Create table #check  (
   select_stmnt varchar(100)
)

INSERT #check  (select_stmnt)
EXEC dbo.yourSproc


select @selcmd  = select_stmnt from #check

and... wrap the ugly t-sql in stored procedure that calls the other stored procedure.  Then not many people are likely to see it.   You could always encrypt it!  :-)

AustinSeven

Why not something like this...

BEGIN TRY
  INSERT MY_TABLE (Column1, Column2) EXEC sp_SELECT
END TRY
BEGIN CATCH
  INSERT MY_TABLE (Column1) EXEC sp_SELECT
END CATCH

Select allOpen in new window

Better yet:

BEGIN TRY
INSERT tTest (Column1, Column2) EXEC sp_SELECT
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 213
  BEGIN
    INSERT tTest (Column1) EXEC sp_SELECT;
  END
END CATCH

Select allOpen in new window

Thejaka,

This code is for SQL 2005 or 2008 I guess ?

Unfortunatly, we're still using SQL 2000.

We are in the process of testing the proposed solutions.

Thanks

Well, I took a guess that you would be using SQL 2000 so no problem.   I've tidied up the demo code as below.   Don't be daunted as most of it is just setting up the demo.   Just copy all the code into QA, select a non-important database into which it's ok to create some temporary tables and run it.   Change the commented out line in the sproc test the one or two column situation.   Just run all the code again as it's repeatable.

AustinSeven

-- simulate sproc that you can't change
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spOutputSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spOutputSelect]
GO
CREATE procedure spOutputSelect
as
-- comment/uncomment the two lines below to replicate what your sproc does
--select 'SELECT col1 FROM test'
select 'SELECT col1, col2 FROM test'
go


-- Simulate table that the select statement uses
create table test (
col1 varchar(50),
col2 varchar(50)
)
-- Insert some data into test table
insert into test values('A','X')
insert into test values('B','Y')
insert into test values('C','Z')


-- Simulate the table that you want to insert into based on the results of the select
create table #test2 (
col1 varchar(50),
col2 varchar(50)
)

-- This section is the example that would be included in your solution as the above is just to setup the demo
create table #sql (
  sqlcmd varchar(100)
)

-- get the select statement output from the sproce into a temp table
insert into #sql exec spOutputSelect

declare @selcmd nvarchar(100)
select @selcmd = sqlcmd from #sql

if CHARINDEX ( ',', @selcmd )  > 0
    insert into #test2 (col1, col2) exec sp_executesql @selcmd
else
   if CHARINDEX ( ',', @selcmd ) = 0
      insert into #test2 (col1) exec sp_executesql @selcmd

-- confirm the output is as exected.  
select * from #test2

-- tidy up
drop table test
drop table #test2
drop table #sql

AustinSeven,
>>All depends on capturing the string output from the sproc into a variable.   If you can do that, this will work as a messy kind of workaround...
I can't capture the string output...

kselvia,
Ugly but it works !!  Thansk alot !

Cool.  You can also create a linked server

sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = 'myserver'

SELECT * into #tmp FROM OPENQUERY(LOCALSERVER,'master.dbo.sp_who')

instead of using SQLOLEDB

You didn't say what causes your procedure to return different results under what conditions but Thejaka's 2K5 solution would end up calling it twice for 1 column.  I'm guessig the first result may or may not indicate how many colums would be returned when you called it again.  Also possibly problematic if it runs for a long time.

For what it's worth, there is actually a way do to it using server side cursors similar to this;

DECLARE @cursor integer

EXEC sp_cursoropen @cursor output, N'exec p_myproc', 4, 2

EXEC sp_cursoroption @cursor, 2, 'myCursor'

If (select count(1) from FROM master.dbo.syscursorcolumns
WHERE cursor_handle = @cursor) > 1
insert MyTable (field1, field2)
    exec sp_cursorprepareexec @cursor
Else
insert MyTable (field1)
    exec sp_cursorprepareexec @cursor

but I haven't tested it.  If it's wrong, it can be made right. If there are a lot of rows you are better off using the linked server.

kselvia,

You're right, my solution would call the sp a second time if the first attempt failed. The INSERT would be performed only once. Whether the solution is viable depends on the behaviour of the SELECT sp.