I have a table called
raw_data that contains a column with a large string of data fields formatted in fixed length sub-strings. I also have a table
table_1 that specifies the column name and the data range in the string for each value. I need to create a SQL
INSERT statement to move data from
raw_data into a table called
table_2 with all the columns.
table_1 has about 600 rows, so I am wondering if I can loop through each record to create the SQL statement that inserts the data into
Table_1 Name Start Length AAA 1 2 BBB 3 3 CCC 6 1
I haven't learned how to use cursors; the below query could be incorrect. There will be 3 tables involved in this task.
table_1 to look up the name, start, length values.
table_2 will be the table I need to insert the data into. The third table
raw_data has the column with the sub-strings of each needed value.
DECLARE @SQL VARCHAR(200) DECLARE @NAME VARCHAR(200) DECLARE @START VARCHAR(200) DECLARE @LENGTH VARCHAR(200) SET @NAME = '' DECLARE Col_Cursor CURSOR FOR SELECT Name, Start, Length FROM ODS_SIEMENS_LAYOUT WHERE RecordType = '1' OPEN Col_Cursor FETCH NEXT FROM Col_Cursor INTO @NAME, @START, @LENGTH WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = @NAME + '=' + 'SUBSTRING(RAW_DATA,' + @START + ',' + @LENGTH + ')' FETCH NEXT FROM Col_Cursor INTO @NAME, @START, @LENGTH END CLOSE Col_Cursor DEALLOCATE Col_Cursor
I need to generate something like the below query:
INSERT INTO TABLE_2 'AAA' = SUBSTRING(RAW_DATA,1,2) 'BBB' = SUBSTRING(RAW_DATA,3,3) 'CCC' = SUBSTRING(RAW_DATA,5,2) ........
Can I loop through each column to form the SQL Statement instead of manually coding 600 columns?