Complicated problem, and a difficult solution, Can Anyone Help?

[klindeman72]

Hi all hope to find you well and happy after a great Halloween. I am stuck between a rock and a hard place in that I know what I want to do with the stored procedure I will outline later, and what I am capable of doing in stored procedures. I have hit the wall of my knowledge level. Let me outline the scenario. I have a file that I have already imported into a table using bcp. I have an application that has already gotten from the user the information pertaining to the columns to map from and too (I will describe this shortly) and I have the layout of the destination column and type layout stored in a table using xml strings (Again explanation shortly).

Okay to go backtrack to the last point. We have a table in our database that houses multiple ‘Field Layouts’ that are used as the destination points in a file to file conversion. The field layouts are described in an xml format string so as to be usable in multiple different application processes. Jumping to the first concept, we have a file sitting in a known location that we must determine if that file is comma delimited or fixed width format. Once we get that information from the user, we use a stored procedure to create a table based on what the user described (The number of columns using row delimiters, or field width, etc. etc.) We then use the bcp tool to import the file’s contents into the table as sometimes these files can reach the +- 10gb size. Once we have the file uploaded, we can then take the headers that were discovered and display the individual fields vertically on the screen (Placing them on the left hand side) and the headers from the xml string described earlier vertically on the right of the screen. We then give the user a chance to ‘Map’ source columns to destination columns. Once that mapping is complete we save the source –to- destination relationship in another table, along with the field size and type as described by the xml. The final process (Here is where I have surpassed my ability) I need to write a stored procedure that will read the column mapping table and create a table based on the destination (xml described) file name, field type, field size, per row, and put in that row the source file data that corresponds to the field mapping settings the user supplied. On to the gritty details: Here is what the mapped_columns table might look like.
Key Name Type Size Nullable
PK job_id int 4 0
client_column_name varchar 100 1
cd_column_name varchar 100 1
corder int 4 1
field_type varchar 25 1
field_size int 4 1

I would also have the Source table created and populated from bcp, here is a short idea of what one of those tables might look like.
PK FirstName varchar 200 1
LastName varchar 200 1
Address varchar 200 1
City varchar 200 1
State varchar 200 1
Zip varchar 200 1
Phone varchar 200 1
DOB varchar 200 1
Zip_4 varchar 200 1

Okay now on to the stored procedure. I will try and explain it before I try and make your eyes bleed. So I start checking


Okay the first thing I knew I was going to have to do was to have the ability to loop through the tables. I refuse to pay for the ballet lessons Microsoft asks me continually to install. Next I insert the data from the source table into a temporary table variable. Next I loop through the records, and perform a bit of hygene before I slap it right back to my screen. At this point I am ready so I start checking to see if I am on my internet, and then I check to see if anyone left beer bottles in breakable places. Hehe, okay back to topic, and there are two parts to this. Part 1 – Is this the best way to write this stored procedure? Should I be using a stored procedure or maybe something else. Thanks!



CREATE PROCEDURE usp_Transform_Working
(
@job_id INT = 10178
)
AS

DECLARE @createtable VARCHAR(8000)
DECLARE @inserttable VARCHAR(8000)
DECLARE @sql VARCHAR(8000)
DECLARE @max_rec_id INT
DECLARE @min_rec_id INT
DECLARE @rec_id INT
DECLARE @client_column_name VARCHAR(100)
DECLARE @cd_column_name VARCHAR(100)
DECLARE @cd_old_column_name VARCHAR(100)
DECLARE @cd_next_column_name VARCHAR(100)
DECLARE @corder INT
DECLARE @field_type VARCHAR(25)
DECLARE @field_size INT
DECLARE @str_field_size VARCHAR(10)



SET @cd_old_column_name = 'FIRSTDEF'

DECLARE @mapped TABLE (
rec_id INT identity(1,1) NOT NULL
,client_column_name VARCHAR(100) NULL
,cd_column_name VARCHAR(100) NULL
,corder INT NULL
,field_type VARCHAR(25) NULL
,field_size INT NULL)

INSERT INTO @mapped(client_column_name,cd_column_name,corder,field_type,field_size)
SELECT client_column_name,cd_column_name,corder,field_type,field_size
FROM clickdata_services..mapped_columns
WHERE job_id = @job_id
ORDER BY cd_column_name,corder

SELECT @rec_id = MIN(rec_id),@max_rec_id = MAX(rec_id)+1 FROM @mapped
SELECT @min_rec_id = MIN(rec_id) FROM @mapped
SET @createtable = 'CREATE TABLE clickdata_services_working.dbo.cd_' + LTRIM(RTRIM(STR(@job_id))) + '_header_out ( '
WHILE @rec_id < @max_rec_id
BEGIN
SELECT @client_column_name = client_column_name
,@cd_column_name = cd_column_name
,@corder = corder
,@field_type = field_type
,@field_size = field_size
FROM @mapped
WHERE rec_id = @rec_id
IF @rec_id > @min_rec_id
BEGIN
SELECT @cd_old_column_name = cd_column_name
FROM @mapped
WHERE rec_id = @rec_id - 1
END
SELECT @cd_next_column_name = cd_column_name
FROM @mapped
WHERE rec_id = @rec_id + 1
IF @cd_column_name <> @cd_old_column_name
BEGIN
IF UPPER(@field_type) = 'CHAR'
BEGIN
SET @createtable = @createtable + @cd_column_name + ' VARCHAR'
END
ELSE
BEGIN
SET @createtable = @createtable + @cd_column_name + ' ' + ISNULL(@field_type, 'VARCHAR') + ' '
END
IF UPPER(@field_type) = 'VARCHAR' OR @field_type IS NULL OR UPPER(@field_type) = 'CHAR'
BEGIN
SET @str_field_size = ISNULL(CAST(@field_size AS VARCHAR), '50')
SET @createtable = @createtable + '(' + @str_field_size + ')'
END

IF @rec_id + 1 < @max_rec_id
BEGIN
SET @createtable = @createtable + ', '
END
END
SET @rec_id = @rec_id + 1
END
SET @createtable =@createtable + ')'


SET @inserttable = 'INSERT INTO clickdata_services_working..cd_' + LTRIM(RTRIM(STR(@job_id))) + '_header_out ('
SELECT @rec_id = MIN(rec_id),@max_rec_id = MAX(rec_id)+1 FROM @mapped
SELECT @min_rec_id = MIN(rec_id) FROM @mapped
SET @sql = ' SELECT LEFT('
WHILE @rec_id < @max_rec_id
BEGIN
SELECT @client_column_name = client_column_name
,@cd_column_name = cd_column_name
,@corder = corder
,@field_type = field_type
,@field_size = field_size
FROM @mapped
WHERE rec_id = @rec_id
IF @rec_id > @min_rec_id
BEGIN
SELECT @cd_old_column_name = cd_column_name
FROM @mapped
WHERE rec_id = @rec_id - 1
END
SELECT @cd_next_column_name = cd_column_name
FROM @mapped
WHERE rec_id = @rec_id + 1
--Check if the column name is the same as the previous one, if it is not then check if we are at the end of the list, if not then we need to add a comma
IF @cd_column_name <> @cd_old_column_name
BEGIN
SET @inserttable = @inserttable + @cd_column_name
IF @rec_id + 1 < @max_rec_id
BEGIN
SET @inserttable = @inserttable + ','
END
END
--If the column name is the same as the previous, then we need to concatinate the two records together
IF @cd_column_name = @cd_old_column_name
BEGIN
SET @sql = @sql + ' + '' '' + ' + @client_column_name
END
ELSE
BEGIN
SET @sql = @sql + ISNULL(@client_column_name + CASE
WHEN @cd_column_name = @cd_old_column_name
THEN ' '
ELSE ' '
END , ''''' ' + ' ')
END
SET @str_field_size = ISNULL(CAST(@field_size AS VARCHAR), '50')
IF UPPER(@cd_column_name) <> UPPER(@cd_next_column_name)
BEGIN
SET @sql = @sql + ', ' + @str_field_size + '), LEFT( '
END
IF @rec_id + 1 = @max_rec_id
BEGIN
SET @sql = @sql + ', ' + @str_field_size + ') '
END
SET @rec_id = @rec_id + 1
END



print @createtable
SET @inserttable =@inserttable + ') '
SET @inserttable = @inserttable + @sql + ' FROM clickdata_services_working..cd_' + LTRIM(RTRIM(STR(@job_id))) + '_data_in'
print @inserttable
IF (SELECT [master].dbo.fn_SecCheckSQL(@createtable))=0
BEGIN
EXEC (@createtable)
END
IF (SELECT [master].dbo.fn_SecCheckSQL(@inserttable))=0
BEGIN
EXEC (@inserttable)
END

GO

 

[zhavic]

Hmmm, there is better way to write this procedure,

This may be one of the ways, it shoving first part - building the CREATE TABLE statement.
It does not using temp table and it using only one varriable to store the result in ( offcourse, there is also @job_id ). Also it assumes that values in corder column are without gaps ( for each job_id )

SET @createtable = 'CREATE TABLE clickdata_services_working.dbo.cd_' + LTRIM(RTRIM(STR(@job_id))) + '_header_out ( '

SELECT @createtable = @createtable
					 +
					 CASE WHEN Previous_column_name.cd_column_name IS NULL OR Previous_column_name.cd_column_name <> mapped.cd_column_name	-- skip the same columns
						  THEN
							  CASE WHEN field_type IS NULL THEN ' VARCHAR'
								   WHEN UPPER( field_type ) = 'CHAR' THEN ' VARCHAR'
								   ELSE field_type
								END
							  +
							  CASE WHEN field_type IS NULL OR UPPER( field_type ) IN ( 'VARCHAR', 'CHAR' )
								   THEN '(' + ISNULL( CAST( field_size  AS VARCHAR ), '50' ) + ')'
								   ELSE ''
								END
							  +
							  /* when not on last column */
							  CASE WHEN mapped.corder < Max_corder.corder THEN ','
								   ELSE ''
								END
						  ELSE ''
					  END
					+
				    /* when on last column */
				    CASE WHEN mapped.corder = Max_corder.corder THEN ')'	-- close columns definition
						 ELSE ''
					  END
FROM clickdata_services..mapped_columns AS mapped
	/* take corder of the last column for given job_id */
	INNER JOIN ( SELECT MAX( corder ) AS corder,
						job_id
					FROM clickdata_services..mapped_columns
					GROUP BY job_id
			   ) AS Max_corder ON Max_corder.job_id = mapped.job_id
	/* take previous column name */
	LEFT JOIN ( SELECT cd_column_name,
					   corder,
					   job_id
					FROM clickdata_services..mapped_columns
			   ) AS Previous_column_name ON Previous_column_name.job_id = mapped.job_id AND Previous_column_name.corder = mapped.corder - 1
WHERE mapped.job_id = @job_id
ORDER BY mapped.cd_column_name, mapped.corder

PRINT @createtable

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[klindeman72]

Wow, I would not have seen that solution. Very elegant if I do say so myself. I am not sure I would have ever gotten to the way you loop through the rows either. I am in your debt so I will bow again and say Thank you very much!

K

 

[zhavic]

I learned it at this forum !

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.