December 4, 2009 at 8:21 am
So... I'm building an excel export feature which will do the following.
1. Take the query and insert it into temptable.
2. Select the columns from the temptable and create a new table using nvarchar(500)'s. (Used through a cursor.)
3. The below code is used to create the 1st and second row to be input into the table.
4. Then A select into is done, and the rest of the data is inserted into the row.
4. ?????
5. Profit.
If there is a.) a better way to do this or b.) a way that you guys know of to fix the cursor I created below I would be very very very appreciative.
-- CREATE INSERT STRING---
--------------------------
Declare @tablename nvarchar(255);
set @tablename = 'Additional_Interest';
DECLARE MY_CURSOR Cursor --- We need to name It!
FOR
select a.name AS NAME,c.F3 AS DESCRIP, d.[Source Table/Column(s)] AS SOURCECOLUMN from sys.columns a
join sys.tables b
on a.object_id = b.object_id
left join [VisionData].[ACORD146] c
on a.name = rtrim(ltrim(c.F2))
left join [loaded].[ACORD146] d
on a.name = rtrim(ltrim(d.[Target Column]))
where b.name = 'temptable'
order by a.column_id asc
Open My_Cursor --- (remember to CLOSE IT LATER)
DECLARE @VAR1 nvarchar(max)
DECLARE @VAR2 nvarchar(max)
DECLARE @VAR3 nvarchar(max)
DECLARE @INSERTSTRING nvarchar(max)
DECLARE @ROW1 nvarchar(max)
DECLARE @ROW2 nvarchar(max)
set @ROW1 = '"';
SET @ROW2 = '"';
set @INSERTSTRING = 'INSERT INTO ' + @tablename + ' ('
Fetch NEXT FROM MY_Cursor INTO @VAR1, @VAR2, @VAR3
While (@@FETCH_STATUS = 0)
BEGIN
SET @INSERTSTRING = @INSERTSTRING + @VAR1 + ','
print 'NEW LOOP'
print @VAR2;
PRINT @VAR3;
SET @ROW1 = @ROW1 + @VAR2 + '","'
SET @ROW2 = @ROW2 + @VAR3 + '","'
print @ROW1
print @ROW2
FETCH NEXT FROM MY_CURSOR INTO @VAR1, @VAR2, @VAR3
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
PRINT @ROW1;
PRINT @ROW2;
PRINT @INSERTSTRING
--------OUTPUT--------
NEW LOOP
NEW LOOP
NEW LOOP
NEW LOOP
NEW LOOP
NEW LOOP
NEW LOOP
NEW LOOP
Additional Interest Number
NEW LOOP
Additional Interest Type Other - Description
APPADD.ADTYPE, ADDINTP.AINAME, ADDINTP.AICITY, ADDINTP.AIZIP, ADDINTP.AIADD1, ADDINTP.AIADD2
NEW LOOP
Scheduled Equipment: Foreign Key: frmACORD146_ScheEquip.EquipStor_Order
APPADD.ADUSQ#
NEW LOOP
Equipment Description
NEW LOOP
Additional Interest Name
ADDINTP.AINAME
NEW LOOP
Additional Interest Address - Street
APPADD.ADTYPE, ADDINTP.AINAME, ADDINTP.AICITY, ADDINTP.AIZIP, ADDINTP.AIADD1, ADDINTP.AIADD2
NEW LOOP
City
ADDINTP.AICITY
NEW LOOP
State/Province: List('',Select USA State {},AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MO,MN,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY,Select CANADA State
ADDINTP.AIST
NEW LOOP
Zip/Postal Code
ADDINTP.AIZIP
NEW LOOP
Certificate Required: Indicates if the additional interest indicated above is to be the payor for this policy.
APPADD.ADCTF
INSERT INTO Additional_Interest (InformationSet,LegacyID,ClientName,ClientType,PolicyNo,EffDate,ExpDate,AddInt_Order,AdditionalInterest_NameAddress_ItemIdInfo_OTHER_Desc,AddlInt_EquipmentNumber,AddlInt_UnschedEquipDesc,AdditionalInterest_NameAddress_Name,AdditionalInterest_NameAddress_Addr1,AdditionalInterest_NameAddress_City,AdditionalInterest_NameAddress_StateProvCd,AdditionalInterest_NameAddress_PostalCode,AdditionalInterest_CertificateReqInd,
December 4, 2009 at 8:27 am
How about this?
http://www.sqlservercentral.com/articles/SQLCLR/68842/
Also, could you wrap that really long line in a code block so that it doesn't hose the formatting of the page?
December 4, 2009 at 8:29 am
Awesome article, it's absolutly amazing, one problem though.... I'm already using it.. 🙂
The issue here is the following.
I need to insert two rows at the begining of the table before I export it to the excel file, and I'm trying to do that through the cursor and preserve the data... Any Suggestions?
December 4, 2009 at 8:34 am
Heh, cool deal. So, I must be missing something here (and the lack of code blocks totally hoses my display so the code is hard to follow, but what is the issue of just doing something like this after you get the data into your initial temp table?
SELECT 'a','b',A.*
INTO #B
FROM #A A
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply