Problem Inserting records per Portfolio into a temp table

Question

Post reply

Problem Inserting records per Portfolio into a temp table

Neil.McKinnon

SSChasing Mays

Points: 655
More actions
June 20, 2013 at 4:56 am

#279300

Hi guys,
I have been trying to update a temporary table with the top 10 holdings per portfolio. I have been using a cursor to populate a variable which will hold each portfolio code in turn and this is used in the insert statement.
However it is not working! The code executes but no records are added to the temp table. Probably something stupid I've missed but if anyone could point me in the right direction I would be very grateful!!
Code is below. Many thanks guys,
Neil
CREATE TABLE #Top10(
PortCode nVarchar,
Classification nvarchar,
Issuer nvarchar,
Total nvarchar
)
DECLARE @portcode2 nvarchar
Declare c Cursor For Select Distinct port_portcode From FactSheet.StagingEquityHoldings
Open c
Fetch next From c into @portcode2
While @@Fetch_Status=0 Begin
INSERT INTO #Top10(
PortCode,
Classification,
Issuer,
Total
)
Select Top 10
Port_Portcode,
Class_0_Minor_Description,
Issuer,
Total_Perc_Port_Mkt
from
FactSheet.StagingEquityHoldings
Where Port_Portcode = @portcode2
Order by
Total_Perc_Port_Mkt Desc
Fetch next From c into @portcode2
End
Select * from #Top10

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

There is no need for a cursor to get the top 10 rows per port_portcode.

Try this (not tested due to lack of sample data though)

;with cTE AS (SELECT Port_Portcode,

Class_0_Minor_Description,

Issuer,

Total_Perc_Port_Mkt,

ROW_NUMBER() OVER (PARTITION BY Port_Portcode ORDER BY Total_Perc_Port_Mkt Desc) AS RowNo

from FactSheet.StagingEquityHoldings)

INSERT INTO #Top10(PortCode, Classification, Issuer, Total)

SELECT Port_Portcode, Class_0_Minor_Description, Issuer, Total_Perc_Port_Mkt

FROM cTE

WHERE RowNo <= 10

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

I agree with the poster above

Going by the description that you have provided even I don't think there is any need for a CURSOR

Please post the table definitions, sample data and the expected results based on the sample data

If you are not sure on how to do this, please check the link in my signature

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Neil.McKinnon SSChasing Mays Points: 655 More actions · Answer 3

Hi Guys thank you for your help!

Here is a sample of the data. Thanks again! :

CREATE TABLE [FactSheet].[StagingEquityHoldings](

[Instrument_Instrument_Id] [nvarchar](50) NULL,

[Class_0_Minor_Description] [nvarchar](50) NULL,

[Issuer] [nvarchar](50) NULL,

[PK_Date] [nvarchar](50) NULL,

[Port_Portcode] [nvarchar](50) NULL,

[Portname] [nvarchar](50) NULL,

[Total_Perc_Port_Mkt] [nvarchar](50) NULL

) ON [PRIMARY]

INSERT INTO [FactSheet].[StagingEquityHoldings]

(Instrument_Instrument_Id, Class_0_Minor_Description, Issuer, PK_Date, Port_Portcode, Portname, Total_Perc_Port_Mkt)

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''6.11' UNION ALL

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.68' UNION ALL

SELECT '2475833','Health Care ','JOHNSON & JOHNSON ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.65' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.68' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''2.83' UNION ALL

SELECT 'B8KF9B4','Consumer Discretionary ','WPP PLC ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''2.70' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.36' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.23' UNION ALL

SELECT '2190385','Financials ','JPMORGAN CHASE & CO ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.39' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.32' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.29' UNION ALL

SELECT '2778844','Health Care ','MERCK & CO INC ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.42' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.13' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''4.13' UNION ALL

SELECT '2380498','Industrials ','GENERAL ELECTRIC CO ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''4.13' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.09' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''1.08' UNION ALL

SELECT '7123870','Consumer Staples ','NESTLE SA ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''1.03' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''5.05' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.51' UNION ALL

SELECT 'B2PKRQ3','Consumer Staples ','PHILIP MORRIS INTL ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.55' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.89' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''2.07' UNION ALL

SELECT '4356646','Materials ','SYNGENTA ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''1.97' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.87' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.02' UNION ALL

SELECT '0237400','Consumer Staples ','DIAGEO ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.04' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''4.86' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''' UNION ALL

SELECT 'B1C4TB0','Information Technology ','TECH MAHINDRA ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.86' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.06' UNION ALL

SELECT 'B2PZN04','Information Technology ','VISA INC ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''3.08' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.81' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''' UNION ALL

SELECT '4942818','Information Technology ','SAMSUNG ELECTRONIC ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.77' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''3.03' UNION ALL

SELECT '5727973','Industrials ','SIEMENS AG ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''2.89' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.69' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''1.12' UNION ALL

SELECT '2569286','Financials ','ICICI BK LTD ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''1.13' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.61' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''2.35' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''' UNION ALL

SELECT '6073556','Telecommunication Services ','CHINA MOBILE ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND''4.61' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','IINTGBP''AGF - STERLING INT''L EQUITY''' UNION ALL

SELECT 'B0SXY97','Financials ','PHOENIX MILLS ','30 Apr 2013','IINTUSD''AGF - DOLLAR INT''L EQUITY''' UNION ALL

SELECT '5086577','Materials ','BASF SE ','30 Apr 2013','I1890764''MAESTRO ASHB GLOBAL CORE EQ''4.61' UNION ALL

SELECT '5086577','Materials ','BASF SE ','30 Apr 2013','I565606''AGF - EUROPEAN EQUITY EUR''' UNION ALL

SELECT '5086577','Materials ','BASF SE ','30 Apr 2013','I565614''AEM - CHINDIA EQUITY FUND''' UNION ALL

SELECT '5086577','Materials ','BASF SE ','30 Apr 2013','I842355''ASH INDIA EQUITY OPPS FUND'''

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Can you also post the expected results based on your sample data along with a brief description of the logic?

It will help us to test the solutions before we post them

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Neil.McKinnon SSChasing Mays Points: 655 More actions · Answer 5

Sure thing!

The purpose is to create a table/recordset that lists the top ten holdings for each portfolio. This is then used by my users to illustrate how the portfolio is made up, a regulatory requirement. For example the IINTUSD portcode will show the following as its top 10:

IINTUSDIndustrials GENERAL ELECTRIC CO 4.13

IINTUSDHealth Care JOHNSON & JOHNSON 3.65

IINTUSDConsumer Staples PHILIP MORRIS INTL 3.55

IINTUSDHealth Care MERCK & CO INC 3.42

IINTUSDFinancials JPMORGAN CHASE & CO 3.39

Many thanks,

Neil

Abu Dina SSChampion Points: 14155 More actions · Answer 6

Have you tried the code I provided above?!

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Neil.McKinnon SSChasing Mays Points: 655 More actions · Answer 7

I did Abu, it returned the following error:

"Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

The statement has been terminated."

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

Neil.McKinnon (6/20/2013)
I did Abu, it returned the following error:
"Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated."

The structure of your temporary table seems to be the problem. Why is everything defined as nvarchar? As column length is not specified, it will take the data type as nvarchar(1).

Please re define the table with data types similar to the "StagingEquityHoldings" table

CREATE TABLE #Top10(

PortCode nVarchar,

Classification nvarchar,

Issuer nvarchar,

Total nvarchar

)

In fact, if you simple want to display the results you can remove the temporary table completely

The final SELECT provided by Abu should give you the desired results.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Neil.McKinnon SSChasing Mays Points: 655 More actions · Answer 9

You were right Kingston!

Big thank you to Abu and yourself for your invaluable help! All working as intended!!

Many thanks

Neil

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 10

Neil.McKinnon (6/20/2013)
You were right Kingston!
Big thank you to Abu and yourself for your invaluable help! All working as intended!!
Many thanks
Neil

Glad we could help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/