July 31, 2009 at 11:57 pm
I guess you've lost me a bit... why do you need this to be dynamic SQL? I believe the example code I posted does everything you've asked.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2009 at 12:27 am
it works
August 1, 2009 at 2:53 am
This works:
declare @temp_table_name varchar(255), @temp_table_name2 varchar(255)
set @temp_table_name = '[staging1]'
set @temp_table_name2 = '[staging2]'
Exec ('SELECT RowNum ,
WholeRow = dbo.fn_true_csv_split(WholeRow,char(9))
INTO ' + @temp_table_name2 + '
FROM ' + @temp_table_name)
I thought I cannot call a function in this Exec... but it works.. Thanks!
August 1, 2009 at 8:07 am
Checking...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2009 at 8:21 am
Yep... that should work.
As a side bar, it's been a day or two since this thread has been active and I orginally didn't believe your code would work because the name of my own function threw me. Most functions with the name "Split" in them return more than one row because they actually split the data from a single "cell" of data to many rows. You used a similar name and it also threw me for a second. My first impression was that your code actually wouldn't work because I didn't remember my own code (I write a lot of code) for this thread (I write a lot of threads) because I assumed the function would return more than one row just because of the name.
Since this function doesn't actually do a split, it may be of future benefit to others to rename the function to something like (following your standard for names) dbo.fn_prep_true_csv_4split.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2009 at 9:11 am
Awesome...Thank you.
August 1, 2009 at 9:34 am
You bet... thanks for the feedback, Ghanta.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2009 at 9:55 pm
Hey Jeff,
This works great except one issue... using CTE to create the final table takes a bit more time than expected... a file with only 10K records takes around 1minute in the CTE part... I was thinking since we will be changing the delimiter from comma to tab or anything that is not expected in the data... can we use bcp or bulk insert to import data from one table to another without exporting to flat file? Maybe there is something I can change to make CTE run faster...
Thanks!
August 13, 2009 at 10:12 pm
Post the code you're currently using and let's have a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2009 at 10:41 pm
This is the code that runs... but my code is in dynamic sql as I will be loading more than one file at onetime and fields and tables will change... Thankyou
;WITH
cteSplit AS
(
SELECT s.RowNum,
Element = ROW_NUMBER() OVER (PARTITION BY s.RowNum ORDER BY s.RowNum, t.N),
RowText = SUBSTRING(s.WholeRow,t.N+1,CHARINDEX('|',s.WholeRow,t.N+1)-t.N-1)
FROM db.dbo.Tally t
CROSS JOIN db.dbo.[staging_1] s
WHERE t.N 1
)
SELECT MAX(CASE WHEN Element = 1 THEN RowText END) AS col1,
MAX(CASE WHEN Element = 2 THEN RowText END) AS col2,
MAX(CASE WHEN Element = 3 THEN RowText END) AS col3,
MAX(CASE WHEN Element = 4 THEN RowText END) AS col4,
MAX(CASE WHEN Element = 5 THEN RowText END) AS col5,
MAX(CASE WHEN Element = 6 THEN RowText END) AS col6,
MAX(CASE WHEN Element = 7 THEN RowText END) AS col7,
MAX(CASE WHEN Element = 8 THEN RowText END) AS col8,
MAX(CASE WHEN Element = 9 THEN RowText END) AS col9,
MAX(CASE WHEN Element = 10 THEN RowText END) AS col10,
MAX(CASE WHEN Element = 11 THEN RowText END) AS col11
INTO db.dbo.[staging_2] FROM cteSplit
GROUP BY RowNum
August 14, 2009 at 12:21 am
Do what the comments in the following state and try it again. Let me know if it helped...
;WITH
cteSplit AS
(
SELECT s.RowNum,
Element = ROW_NUMBER() OVER (PARTITION BY s.RowNum ORDER BY s.RowNum, t.N),
RowText = SUBSTRING(s.WholeRow,t.N+1,CHARINDEX('|',s.WholeRow,t.N+1)-t.N-1)
FROM db.dbo.Tally t
CROSS JOIN db.dbo.[staging_1] s
WHERE t.N 1[/strike] --TAKE THIS OUT)
SELECT MAX(CASE WHEN Element = 1 THEN RowText END) AS col1,
MAX(CASE WHEN Element = 2 THEN RowText END) AS col2,
MAX(CASE WHEN Element = 3 THEN RowText END) AS col3,
MAX(CASE WHEN Element = 4 THEN RowText END) AS col4,
MAX(CASE WHEN Element = 5 THEN RowText END) AS col5,
MAX(CASE WHEN Element = 6 THEN RowText END) AS col6,
MAX(CASE WHEN Element = 7 THEN RowText END) AS col7,
MAX(CASE WHEN Element = 8 THEN RowText END) AS col8,
MAX(CASE WHEN Element = 9 THEN RowText END) AS col9,
MAX(CASE WHEN Element = 10 THEN RowText END) AS col10,
MAX(CASE WHEN Element = 11 THEN RowText END) AS col11
INTO db.dbo.[staging_2] FROM cteSplit
GROUP BY RowNum
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 12:53 pm
I made the change you suggested... I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
August 14, 2009 at 1:17 pm
Heh... yeah, I forgot about that. Add TOP 2000000000 to the SELECT in the CTE. Don't use TOP 100%... although I haven't had the time to verify it, rumor has it that TOP 100% has some type of performance problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 1:32 pm
Thanks Jeff... there was little bit of improvement... I would say around 5% of improvement. I will run few more times with different file size and check out the difference. Thank you.
August 14, 2009 at 2:28 pm
Is the "wholerow" text stored as VARCHAR(8000) or VARCHAR(MAX)? If it's VARCHAR(MAX), does it need to be? Since VARCHAR(MAX) is inherently "out of row" (or whatever you want to call it), joins with the Tally table tend to be a bit slow. There is an alternate for VARCHAR(MAX) splits... I just don't have it at work.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply