| Author |
 Topic  |
|
|
PKD
Starting Member
USA
2 Posts |
 Posted - 11/08/2012 : 13:36:45
|
I've got a large data import coming in from a customer. The data comes in in a variant amount, and I'm writing some SQL to tear it apart, drop it into a staging table, and then use that table to update the correct data tables. I've written a UDF to take the initial data and split it on a specified delimiter.
Data example:
DECLARE @BigStringBlob NVARCHAR(MAX);
SET @BigStringBlob = CAST('(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 16, 3, 3, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 17, 10, 10, ''1'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 248, 0, 2, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', True, True, True, True, True, True, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)^(442866, 268, 0, 0, ''2'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ''11/8/2012 9:15:49 AM'', False, False, False, False, False, False, False)' AS NVARCHAR(MAX));
Code:
--Clear Parsed Chunks
DELETE FROM [tmpParsedChunks];
--Fill perma-temp table with initially parsed chunks
--INSERT INTO [tmpParsedChunks]
SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];
The above code results in [tmpParsedChunks] having 20 full rows of data, and the 21st row cutting off halfway.
UID (No column name)
1 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
2 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
3 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
4 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
5 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
6 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
7 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
8 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
9 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
10 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
11 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
12 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
13 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
14 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
15 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
16 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
17 (442866, 16, 3, 3, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
18 (442866, 17, 10, 10, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
19 (442866, 248, 0, 2, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 1, 1, 1, 1, 1, 1, 0)
20 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11/8/2012 9:15:49 AM', 0, 0, 0, 0, 0, 0, 0)
21 (442866, 268, 0, 0, '2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
row 21 is where 'death' happens.
Problem is, I'm going to have far more than 20 rows of usable data being processed, so I need to resolve this issue and allow more text! |
|
|
mandm
Starting Member
49 Posts |
Posted - 11/08/2012 : 13:45:55
|
What data type(s) is your UDF working with?
SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];
|
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 11/08/2012 : 13:49:52
|
| Does it die on the 21st row because that is where it is exceeding 8000 characters in the input string? I would look in the udfCharacterListToTable to see if it is really able to handle nvarchar(max). |
|
|
|
PKD
Starting Member
USA
2 Posts |
Posted - 11/08/2012 : 13:55:02
|
quote:
Originally posted by mandm
What data type(s) is your UDF working with?
SELECT [UID],
REPLACE(REPLACE([Values], 'True', '1'), 'False', '0')
FROM [dbo].[udfCharacterListToTable](@BigStringBlob, '^') AS UCLTT
ORDER BY UCLTT.[UID];
SELECT * FROM [tmpParsedChunks];
THAT was the exact clue-in I needed!
My UDF was using NVARCHAR(1000) strings for each 'row' of data. I changed it to MAX, and presto, all's well!
Thank you! |
|
|
|
mandm
Starting Member
49 Posts |
Posted - 11/08/2012 : 16:40:03
|
| You're welcome. |
|
|
| |
Topic |
|
|
|
Massive Data Input being truncated by N/VChar(MAX)
