?
Solved

T-SQL Convert data in Temp Table rows to INSERT in Table fields

Posted on 2009-07-14
5
Medium Priority
?
422 Views
Last Modified: 2012-05-07
I have a condensed string of data in a single field in one table (delimited by a character) which I can read and extract into a temporary table (e.g. str_DataValue, RowID) giving me 105 rows each with the RowId and actual data value.  (This will remain constant -- hence why I'm trying to write a SP to do this every day.)  However, I'm struggling with taking these 105 individual pieces of data and inserting them into separate fields in another table.  I had hoped that I could iterate through a loop which would allow me to read down the @tbl_TmpDataItems and INSERT each individual Row's data into a field in another table (i.e. 1 -->105 Rows ==> 1 Row but across 105 columns) but without field names this is apparently not possible.  So I'm at the point where I'm declaring 105 variables, defining 105 fields and ending up with a huge CASE statement based upon the RowID of the extracted data.  That I could live with but I can't get the CASE statement to work.  HELP!!!!
When I segment and test the code I can output the @tbl_TmpDataItems showing the extracted data in 105 rows with RowID.  The purpose of the CASE statement being simply to assign each row of the @tbl_TmpDataItems to a user variable which would allow me to use an INSERT/UPDATE to write them to a single row of another table.  Only then can I begin the next stage which is SQL Reporting -- are you ready for the journey???
USE XYZ;
 
DECLARE @int_CostingCurrentFieldIndex	int
DECLARE	@int_CostingMaxFieldIndex		int
DECLARE	@str_DataHeadings				varchar(4096)
DECLARE	@str_HeadingValue				varchar(100)
DECLARE	@str_DataString					varchar(2048)
DECLARE @str_DataItem					varchar(100)
DECLARE @int_RecCropCurrentIndex		int
DECLARE @int_RecCropMaxIndex			int
DECLARE @int_StringCurrentIndex			int
DECLARE @int_StringMaxIndex				int
 
DECLARE @var_001						varchar(255)
DECLARE @var_002						varchar(6)
DECLARE @var_003						int
DECLARE @var_004						varchar(50)
DECLARE @var_005						char
DECLARE @var_006						int
DECLARE @var_007						float
DECLARE @var_008						int
DECLARE @var_009						datetime
DECLARE @var_010						datetime
DECLARE @var_011						int
DECLARE @var_012						int
DECLARE @var_013						int
DECLARE @var_014						int
DECLARE @var_015						int
DECLARE @var_016						float
DECLARE @var_017						float
DECLARE @var_018						float
DECLARE @var_019						float
DECLARE @var_020						float
DECLARE @var_021						float
DECLARE @var_022						money
DECLARE @var_023						money
DECLARE @var_024						varchar(255)
DECLARE @var_025						money
DECLARE @var_026						int
DECLARE @var_027						money
DECLARE @var_028						varchar(255)
DECLARE @var_029						money
DECLARE @var_030						int
DECLARE @var_031						money
DECLARE @var_032						varchar(255)
DECLARE @var_033						money
DECLARE @var_034						int
DECLARE @var_035						money
DECLARE @var_036						varchar(255)
DECLARE @var_037						money
DECLARE @var_038						int
DECLARE @var_039						money
DECLARE @var_040						varchar(255)
DECLARE @var_041						money
DECLARE @var_042						int
DECLARE @var_043						money
DECLARE @var_044						int
DECLARE @var_045						money
DECLARE @var_046						money
DECLARE @var_X01						money
DECLARE @var_047						money
DECLARE @var_048						money
DECLARE @var_049						money
DECLARE @var_050						money
DECLARE @var_051						money
DECLARE @var_052						money
DECLARE @var_053						money
DECLARE @var_054						money
DECLARE @var_X02						money
DECLARE @var_055						money
DECLARE @var_X03						money
DECLARE @var_056						money
DECLARE @var_X04						money
DECLARE @var_057						money
DECLARE @var_058						money
DECLARE @var_059						money
DECLARE @var_060						money
DECLARE @var_061						money
DECLARE @var_062						money
DECLARE @var_063						money
DECLARE @var_064						money
DECLARE @var_065						money
DECLARE @var_066						money
DECLARE @var_067						varchar(255)
DECLARE @var_068						float
DECLARE @var_069						float
DECLARE @var_070						float
DECLARE @var_071						money
DECLARE @var_072						money
DECLARE @var_073						datetime
DECLARE @var_074						datetime
DECLARE @var_075						money
DECLARE @var_076						float
DECLARE @var_077						int
DECLARE @var_078						money
DECLARE @var_079						int
DECLARE @var_080						float
DECLARE @var_081						float
DECLARE @var_082						int
DECLARE @var_083						float
DECLARE @var_084						money
DECLARE @var_085						int
DECLARE @var_086						money
DECLARE @var_087						int
DECLARE @var_088						money
DECLARE @var_089						int
DECLARE @var_090						money
DECLARE @var_091						int
DECLARE @var_092						money
DECLARE @var_093						int
DECLARE @var_094						int
DECLARE @var_095						int
DECLARE @var_096						float
DECLARE @var_097						float
DECLARE @var_098						float
DECLARE @var_099						float
DECLARE @var_100						float
DECLARE @var_101						int
DECLARE @var_102						int
DECLARE @var_103						money
DECLARE @var_104						int
DECLARE @var_105						float
 
DECLARE @tbl_TmpDataItems	TABLE	(
										str_DataValue	varchar(100),
										int_RowID int IDENTITY PRIMARY KEY
)
 
DECLARE @tbl_CostingData	TABLE (
	int_RowID						int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	str_TmpCostType					varchar(60) NOT NULL,
	str_TmpCropId					varchar(6) NOT NULL,
	str_TmpData						varchar(2048) NOT NULL,
	str_TmpHeadings					varchar(4096),
	str_TmpStatus					varchar(10),
	str_TmpUser						varchar(20),
	dtm_TmpDateCreated				datetime NOT NULL,
	dtm_TmpDateApproved				datetime,
	dtm_TmpDatePaid					datetime,
	str_TmpCheckbookId				varchar(20),
	dbl_TmpAudit					float,
	int_TmpOption					smallint NOT NULL,
	int_TmpCosting					int NOT NULL,
	int_TmpCNTId					int NOT NULL,
	str_001							varchar(255),
	str_002							varchar(6),
	int_003							int,
	str_004							varchar(50),
	str_005							char,
	int_006							int,
	flt_007							float,
	int_008							int,
	dtm_009							datetime,
	dtm_010							datetime,
	int_011							int,
	int_012							int,
	int_013							int,
	int_014							int,
	int_015							int,
	flt_016							float,
	flt_017							float,
	flt_018							float,
	flt_019							float,
	flt_020							float,
	flt_021							float,
	cur_022							money,
	cur_023							money,
	str_024							varchar(255),
	cur_025							money,
	int_026							int,
	cur_027							money,
	str_028							varchar(255),
	cur_029							money,
	int_030							int,
	cur_031							money,
	str_032							varchar(255),
	cur_033							money,
	int_034							int,
	cur_035							money,
	str_036							varchar(255),
	cur_037							money,
	int_038							int,
	cur_039							money,
	str_040							varchar(255),
	cur_041							money,
	int_042							int,
	cur_043							money,
	int_044							int,
	cur_045							money,
	cur_046							money,
	cur_X01							money,
	cur_047							money,
	cur_048							money,
	cur_049							money,
	cur_050							money,
	cur_051							money,
	cur_052							money,
	cur_053							money,
	cur_054							money,
	cur_X02							money,
	cur_055							money,
	cur_X03							money,
	cur_056							money,
	cur_X04							money,
	cur_057							money,
	cur_058							money,
	cur_059							money,
	cur_060							money,
	cur_061							money,
	cur_062							money,
	cur_063							money,
	cur_064							money,
	cur_065							money,
	cur_066							money,
	str_067							varchar(255),
	flt_068							float,
	flt_069							float,
	flt_070							float,
	cur_071							money,
	cur_072							money,
	dtm_073							datetime,
	dtm_074							datetime,
	cur_075							money,
	flt_076							float,
	int_077							int,
	cur_078							money,
	bln_079							int,
	flt_080							float,
	flt_081							float,
	bln_082							int,
	flt_083							float,
	cur_084							money,
	int_085							int,
	cur_086							money,
	int_087							int,
	cur_088							money,
	int_089							int,
	cur_090							money,
	int_091							int,
	cur_092							money,
	int_093							int,
	int_094							int,
	int_095							int,
	flt_096							float,
	flt_097							float,
	flt_098							float,
	flt_099							float,
	flt_100							float,
	int_101							int,
	int_102							int,
	cur_103							money,
	bln_104							int,
	flt_105							float
)
 
INSERT INTO @tbl_CostingData(
		str_TmpCostType, str_TmpCropId, str_TmpData, str_TmpHeadings, str_TmpStatus, str_TmpUser, dtm_TmpDateCreated, dtm_TmpDateApproved, dtm_TmpDatePaid,
				str_TmpCheckbookId, dbl_TmpAudit, int_TmpOption, int_TmpCosting, int_TmpCNTId
	)
	SELECT strCostType, strCropId, strData, strHead, strStatus, strUser, dtmDateCreated, dtmDateApproved, dtmDatePaid,
				strCheckbookId, dblAudit, intOption, intCosting, CntId
	FROM [ACCOUNTS_FLDS].[dbo].[tblTurkeyCostings]
	WHERE (strStatus = 'TBA') OR (strStatus = 'FIN')
	ORDER BY dtmDateCreated ASC
 
 
SELECT @int_RecCropCurrentIndex = MIN(int_RowId) FROM @tbl_CostingData
SELECT @int_RecCropMaxIndex = MAX(int_RowId) FROM @tbl_CostingData
 
WHILE @int_RecCropCurrentIndex <= @int_RecCropMaxIndex
BEGIN
 
	SET @str_DataString = (	SELECT str_TmpData
							FROM @tbl_CostingData
							WHERE int_RowID = @int_RecCropCurrentIndex)
	
	WITH DataListCTE (StartPos, EndPos) AS 
		( SELECT 1 AS StartPos, CHARINDEX('|' , @str_DataString + '|') AS EndPos
			UNION ALL
				SELECT EndPos + 1 AS StartPos , CHARINDEX('|',@str_DataString + '|' , EndPos + 1) AS EndPos
				FROM DataListCTE 
				WHERE CHARINDEX('|', @str_DataString + '|', EndPos + 1) <> 0
		) 
 
	INSERT INTO @tbl_TmpDataItems  
					SELECT SUBSTRING(@str_DataString, StartPos, EndPos-StartPos)
					FROM DataListCTE
					OPTION (MAXRECURSION 150)
 
	SELECT @int_StringCurrentIndex = MIN(int_RowId) FROM @tbl_TmpDataItems
	SELECT @int_StringMaxIndex = MAX(int_RowId) FROM @tbl_TmpDataItems
 
	WHILE @int_StringCurrentIndex <= @int_StringMaxIndex
	BEGIN
 
		SELECT str_DataValue,
			CASE int_RowID
				WHEN	1	THEN SET @var_001 = str_DataValue
				WHEN	2	THEN SET @var_002 = str_DataValue
				WHEN	3	THEN SET @var_003 = str_DataValue
				WHEN	4	THEN SET @var_004 = str_DataValue
				WHEN	5	THEN SET @var_005 = str_DataValue
				WHEN	6	THEN SET @var_006 = str_DataValue
				WHEN	7	THEN SET @var_007 = str_DataValue
				WHEN	8	THEN SET @var_008 = str_DataValue
				WHEN	9	THEN SET @var_009 = str_DataValue
				WHEN	10	THEN SET @var_010 = str_DataValue
				WHEN	11	THEN SET @var_011 = str_DataValue
				WHEN	12	THEN SET @var_012 = str_DataValue
				WHEN	13	THEN SET @var_013 = str_DataValue
				WHEN	14	THEN SET @var_014 = str_DataValue
				WHEN	15	THEN SET @var_015 = str_DataValue
				WHEN	16	THEN SET @var_016 = str_DataValue
				WHEN	17	THEN SET @var_017 = str_DataValue
				WHEN	18	THEN SET @var_018 = str_DataValue
				WHEN	19	THEN SET @var_019 = str_DataValue
				WHEN	20	THEN SET @var_020 = str_DataValue
				WHEN	21	THEN SET @var_021 = str_DataValue
				WHEN	22	THEN SET @var_022 = str_DataValue
				WHEN	23	THEN SET @var_023 = str_DataValue
				WHEN	24	THEN SET @var_024 = str_DataValue
				WHEN	25	THEN SET @var_025 = str_DataValue
				WHEN	26	THEN SET @var_026 = str_DataValue
				WHEN	27	THEN SET @var_027 = str_DataValue
				WHEN	28	THEN SET @var_028 = str_DataValue
				WHEN	29	THEN SET @var_029 = str_DataValue
				WHEN	30	THEN SET @var_030 = str_DataValue
				WHEN	31	THEN SET @var_031 = str_DataValue
				WHEN	32	THEN SET @var_032 = str_DataValue
				WHEN	33	THEN SET @var_033 = str_DataValue
				WHEN	34	THEN SET @var_034 = str_DataValue
				WHEN	35	THEN SET @var_035 = str_DataValue
				WHEN	36	THEN SET @var_036 = str_DataValue
				WHEN	37	THEN SET @var_037 = str_DataValue
				WHEN	38	THEN SET @var_038 = str_DataValue
				WHEN	39	THEN SET @var_039 = str_DataValue
				WHEN	40	THEN SET @var_040 = str_DataValue
				WHEN	41	THEN SET @var_041 = str_DataValue
				WHEN	42	THEN SET @var_042 = str_DataValue
				WHEN	43	THEN SET @var_043 = str_DataValue
				WHEN	44	THEN SET @var_044 = str_DataValue
				WHEN	45	THEN SET @var_045 = str_DataValue
				WHEN	46	THEN SET @var_046 = str_DataValue
				WHEN	47	THEN SET @var_047 = str_DataValue
				WHEN	48	THEN SET @var_048 = str_DataValue
				WHEN	49	THEN SET @var_049 = str_DataValue
				WHEN	50	THEN SET @var_050 = str_DataValue
				WHEN	51	THEN SET @var_051 = str_DataValue
				WHEN	52	THEN SET @var_052 = str_DataValue
				WHEN	53	THEN SET @var_053 = str_DataValue
				WHEN	54	THEN SET @var_054 = str_DataValue
				WHEN	55	THEN SET @var_055 = str_DataValue
				WHEN	56	THEN SET @var_056 = str_DataValue
				WHEN	57	THEN SET @var_057 = str_DataValue
				WHEN	58	THEN SET @var_058 = str_DataValue
				WHEN	59	THEN SET @var_059 = str_DataValue
				WHEN	60	THEN SET @var_060 = str_DataValue
				WHEN	61	THEN SET @var_061 = str_DataValue
				WHEN	62	THEN SET @var_062 = str_DataValue
				WHEN	63	THEN SET @var_063 = str_DataValue
				WHEN	64	THEN SET @var_064 = str_DataValue
				WHEN	65	THEN SET @var_065 = str_DataValue
				WHEN	66	THEN SET @var_066 = str_DataValue
				WHEN	67	THEN SET @var_067 = str_DataValue
				WHEN	68	THEN SET @var_068 = str_DataValue
				WHEN	69	THEN SET @var_069 = str_DataValue
				WHEN	70	THEN SET @var_070 = str_DataValue
				WHEN	71	THEN SET @var_071 = str_DataValue
				WHEN	72	THEN SET @var_072 = str_DataValue
				WHEN	73	THEN SET @var_073 = str_DataValue
				WHEN	74	THEN SET @var_074 = str_DataValue
				WHEN	75	THEN SET @var_075 = str_DataValue
				WHEN	76	THEN SET @var_076 = str_DataValue
				WHEN	77	THEN SET @var_077 = str_DataValue
				WHEN	78	THEN SET @var_078 = str_DataValue
				WHEN	79	THEN SET @var_079 = str_DataValue
				WHEN	80	THEN SET @var_080 = str_DataValue
				WHEN	81	THEN SET @var_081 = str_DataValue
				WHEN	82	THEN SET @var_082 = str_DataValue
				WHEN	83	THEN SET @var_083 = str_DataValue
				WHEN	84	THEN SET @var_084 = str_DataValue
				WHEN	85	THEN SET @var_085 = str_DataValue
				WHEN	86	THEN SET @var_086 = str_DataValue
				WHEN	87	THEN SET @var_087 = str_DataValue
				WHEN	88	THEN SET @var_088 = str_DataValue
				WHEN	89	THEN SET @var_089 = str_DataValue
				WHEN	90	THEN SET @var_090 = str_DataValue
				WHEN	91	THEN SET @var_091 = str_DataValue
				WHEN	92	THEN SET @var_092 = str_DataValue
				WHEN	93	THEN SET @var_093 = str_DataValue
				WHEN	94	THEN SET @var_094 = str_DataValue
				WHEN	95	THEN SET @var_095 = str_DataValue
				WHEN	96	THEN SET @var_096 = str_DataValue
				WHEN	97	THEN SET @var_097 = str_DataValue
				WHEN	98	THEN SET @var_098 = str_DataValue
				WHEN	99	THEN SET @var_099 = str_DataValue
				WHEN	100	THEN SET @var_100 = str_DataValue
				WHEN	101	THEN SET @var_101 = str_DataValue
				WHEN	102	THEN SET @var_102 = str_DataValue
				WHEN	103	THEN SET @var_103 = str_DataValue
				WHEN	104	THEN SET @var_104 = str_DataValue
				WHEN	105	THEN SET @var_105 = str_DataValue
		FROM @tbl_TmpDataItems
		WHERE int_RowID = @int_StringCurrentIndex
 
		SET @int_StringCurrentIndex = @int_StringCurrentIndex +1
 
	END
 
	SET @int_RecCropCurrentIndex = @int_RecCropCurrentIndex + 1
END
--  Solely for testing purposes...
-- SELECT *
-- FROM @tbl_CostingData
 
-- SELECT *
-- FROM @tbl_TmpDataItems

Open in new window

0
Comment
Question by:PaulL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24855180
have a condensed string of data in a single field in one table (delimited by a character) which I can read and extract into a temporary table (e.g. str_DataValue, RowID) giving me 105 rows each with the RowId and actual data value.
Why don't you import the data as a CSV file (using SSIS)?  You should be able to define the delimiter character and simply extract the columns of data based upon that.  I would still import it into a staging table that has an Identity column, if for no other reason than you may want to reformat and/or scrub the imported data before actually putting it into your production data.
0
 

Author Comment

by:PaulL
ID: 24855235
Is this an option for MS SQL 2000?  I only saw the MS SQL 2005 forum listed.
0
 

Author Comment

by:PaulL
ID: 24855276
I'm looking to set up a job that runs once a day and, to date, I've been doing this manually by taking the data into Excel and expanding the data out then using the DTS Wizard.  My thinking was to encapsulate the process entirely within a SP but maybe I'm not seeing the wood for the trees.  Maybe I'm making it more complicated than it needs to be.  Should I be looking at DTS?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24855484
>>Is this an option for MS SQL 2000?<<
Replace SSIS with DTS.

>>I only saw the MS SQL 2005 forum listed.<<
For SQL Server 2000 you should post in:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/ or
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/
0
 

Accepted Solution

by:
PaulL earned 0 total points
ID: 25211297
In the end I did a recursive S&R on the condensed string and am now able to populate fields across the row.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question