Using a Table of Column Definitions to Create an Insert Query

Question

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_2.

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?

Answer 1

At the risk of sounding like Clippy... it looks like you're trying to import a flat file. Is your RAW_DATA coming from a flat file somewhere? If so you might look into using bulk insert:

Use a Format File to Bulk Import Data

If you are just asking how can you build your sql statement using the data from your column definition table... then the code you have is very close. You want something like this:

DECLARE @COLUMNS varchar(max)
DECLARE @SUBCOLUMNS varchar(max)
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

set @SUBCOLUMNS = ''
set @COLUMNS = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COLUMNS = @COLUMNS + @NAME  + ','
SET @SUBCOLUMNS = @SUBCOLUMNS + 'SUBSTRING(RAW_DATA,' + @START + ',' + @LENGTH + '),'
FETCH NEXT FROM Col_Cursor INTO @NAME, @START, @LENGTH
END


CLOSE Col_Cursor
DEALLOCATE Col_Cursor

set @COLUMNS = LEFT(@COLUMNS, len(@COLUMNS)-1) --get rid of last comma
set @SUBCOLUMNS = LEFT(@SUBCOLUMNS, len(@SUBCOLUMNS)-1) --get rid of last comma
print 'INSERT INTO TABLE_2 ' + '(' + @COLUMNS + ') SELECT ' + @SUBCOLUMNS + ' FROM RawDataTable'

You can take the text that prints and insert that SQL statement into your procedure that does the actual inserts.

Answer 2

Ahh I think I am beginning to unravel what you are trying to do. There is no need for a cursor or dynamic sql here at all. You just need to use a select statement as the values for your insert. Something like this maybe??

INSERT INTO TABLE_2(AAA, BBB, CCC)
SELECT SUBSTRING(RAW_DATA,1,2)          
, SUBSTRING(RAW_DATA,3,3)              
, SUBSTRING(RAW_DATA,5,2)      
FROM ODS_SIEMENS_LAYOUT 
WHERE RecordType = '1'