Solved

Revise an existing stored procedure to...

Posted on 2013-05-10
6
337 Views
Last Modified: 2013-05-11
Using a wonderful solution from Sharath_123 at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28123940.html#a39157260

(also see: http://sqlfiddle.com/#!3/35033/3 for sample to test it)

This solution works with tblsource having only two fields f1 and f2.

New requirement: Put the content of tblSource to tblTarget (distinct and sorted, ignoring blank entries) as shown below.

Now, tblSource has unknown number of fields f1, f2, f3,... fn and tblTarget doesn't exist (the procedure is to build it and populate it).

tblSource (exists in the database)
===============
f1         f2              fn
-----   -------   ... ----------
C          R               Q
B          X              
D          
B          P              P
A          X


 tblTarget (doesn't exist, the stored procedure expected build and populated it)
===============
f1         f2            fn
-----   -------  ... ----------
A          P             P
B          R             Q
C          X
D          
=========================
Note, in tblSource C matches R where as in tblTarget C doesn't match R and that is okay.

Question: Is something like this possible?

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 3
  • 3
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39157355
Its possible. but jsut want to know the max. no. of columns?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39157365
Lets say 10 fields. It will be wonderful if we pass it via a parameter to the stored procedure. Like:

exec abcd 'tblSource','tblTarget', 10  

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39157405
try this sproc. I am creating the target table inside the sproc. no need to create it.
when you execute the sproc, it will create the target table and load the data into it.
create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
)
AS
BEGIN
declare @select varchar(max),@where varchar(max),@sql varchar(max)

-- Create the target table
select @sql = '
IF OBJECT_ID('''+@tblTarget+''') IS NOT NULL
    DROP TABLE ' + @tblTarget +'
	select * into '+@tblTarget+' from ' + @tblSource + ' where 1 = 0'
exec(@sql)

SELECT @where = STUFF(( SELECT ',['+ COLUMN_NAME+']'
                FROM  INFORMATION_SCHEMA.columns where table_name = @tblsource
               ORDER BY   ',['+ COLUMN_NAME+']'
                 FOR XML PATH('')), 1, 1, '') 

select @select = substring(select_list,1,len(select_list)-5)
  from (
select stuff((select 'SELECT ''' + COLUMN_NAME + ''' colname,'+COLUMN_NAME+' data FROM ' + @tblsource + ' WHERE ISNULL('+COLUMN_NAME+','''') <> '''' UNION '  
                from INFORMATION_SCHEMA.columns where table_name = @tblsource
               order by COLUMN_NAME 
                 for xml path(''), 
                type).value('.','nvarchar(max)')
               , 1, 0, '') as select_list) as t1 


select @sql = '
;with cte as (
select *,ROW_NUMBER() over (partition by colname order by data) rn
from ('+@select+') t1)
insert ' + @tblTarget + '
 select '+@where+'
  from cte
pivot (max(data) for colname in ('+@where+') ) p'
exec(@sql)
end

Open in new window


tested here: http://sqlfiddle.com/#!3/1c84c/4
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39157418
Well, you want to limit the no. of columns? see this.
create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
,@no_of_cols int
)
AS
BEGIN
declare @select varchar(max),@where varchar(max),@sql varchar(max)

-- Get the column names
SELECT @where = STUFF(( SELECT ',['+ COLUMN_NAME+']'
                FROM  INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               ORDER BY   ',['+ COLUMN_NAME+']'
                 FOR XML PATH('')), 1, 1, '') 

-- Create the target table
select @sql = '
IF OBJECT_ID('''+@tblTarget+''') IS NOT NULL
    DROP TABLE ' + @tblTarget +'
	select '+@where+ ' into '+@tblTarget+' from ' + @tblSource + ' where 1 = 0'
exec(@sql)

select @select = substring(select_list,1,len(select_list)-5)
  from (
select stuff((select 'SELECT ''' + COLUMN_NAME + ''' colname,'+COLUMN_NAME+' data FROM ' + @tblsource + ' WHERE ISNULL('+COLUMN_NAME+','''') <> '''' UNION '  
                from INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               order by COLUMN_NAME 
                 for xml path(''), 
                type).value('.','nvarchar(max)')
               , 1, 0, '') as select_list) t1 


select @sql = '
;with cte as (
select *,ROW_NUMBER() over (partition by colname order by data) rn
from ('+@select+') t1)
insert ' + @tblTarget + '
 select '+@where+'
  from cte
pivot (max(data) for colname in ('+@where+') ) p'
exec(@sql)
end

Open in new window


tested samples: http://sqlfiddle.com/#!3/cd529/2
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 39158488
Wow, this is wonderful. Thank you very much.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39158665
Hi  Sharath_123,

Once again to improve it a bit, I have a foolow up question at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28125336.html

If you have couple of minutes to take a look at it.

Thanks,

Mike
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to return total 6 19
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
Help Required 3 97
why sql server only update some statistics in the database ? 3 24
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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