Solved

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

Posted on 2009-07-14
5
399 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now