Solved

INSERT INTO with variable number of columns

Posted on 2007-11-16
13
269 Views
Last Modified: 2012-05-05
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
Comment
Question by:jmantha709
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 19

Expert Comment

by:Limbeck
Comment Utility
why not alter your storedproc to either return Select field1,field2 or select field1,null?
0
 
LVL 15

Author Comment

by:jmantha709
Comment Utility
It's not my stored proc, and with be a "big deal" to change it...
0
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:Thejaka
Comment Utility
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
 
LVL 8

Expert Comment

by:Thejaka
Comment Utility
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
 
LVL 15

Author Comment

by:jmantha709
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
 
LVL 15

Author Comment

by:jmantha709
Comment Utility
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
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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
 
LVL 8

Expert Comment

by:Thejaka
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now