Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

INSERT INTO with variable number of columns

Hi Experts,

I have a stored procedure that returns a SELECT statement but that could either have one or two columns in it :
SELECT Field1, Field2
FROM MyTable

OR

SELECT Field1
FROM MyTable

I don't know in advance, what that SP will return and I can't modify it...

I would like to do an INSERT INTO statement into a 2 column table (the second column accepts NULLs) with the valuses returned from my SP.  But how can I build this statement not knowing if I'll get one or two columns ?  Is this possible ?

Thanks
0
jmantha709
Asked:
jmantha709
  • 4
  • 3
  • 3
  • +2
1 Solution
 
LimbeckCommented:
why not alter your storedproc to either return Select field1,field2 or select field1,null?
0
 
jmantha709Author Commented:
It's not my stored proc, and with be a "big deal" to change it...
0
 
AustinSevenCommented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ken SelviaRetiredCommented:
This is really ugly

SELECT * into #tmp FROM
OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=myserver;', 'EXEC master.dbo.sp_who')

If (select count(1) from tempdb..syscolumns where id = object_id('tempdb..#tmp')) > 1
Insert Mytable
  Select Field1, Field2 from #tmp
Else
Insert Mytable
  Select Field1 from #tmp
0
 
AustinSevenCommented:
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
0
 
AustinSevenCommented:
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
0
 
ThejakaCommented:
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

Open in new window

0
 
ThejakaCommented:
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

Open in new window

0
 
jmantha709Author Commented:
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
0
 
AustinSevenCommented:
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
0
 
jmantha709Author Commented:
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 !
0
 
Ken SelviaRetiredCommented:
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.
0
 
ThejakaCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now