Solved

INSERT INTO with variable number of columns

Posted on 2007-11-16
13
273 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
ID: 20298086
why not alter your storedproc to either return Select field1,field2 or select field1,null?
0
 
LVL 15

Author Comment

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

Expert Comment

by:AustinSeven
ID: 20298254
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 20298305
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
ID: 20298376
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
ID: 20298398
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
 
LVL 8

Expert Comment

by:Thejaka
ID: 20298826
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
ID: 20298851
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
ID: 20299208
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
ID: 20299750
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
ID: 20301182
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
ID: 20302080
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
ID: 20304051
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

839 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