very difficult question for data transformation project

[znet]

Hello everyone,
I met a very difficult point when processing data transformation. The case is :
Need import customer's data files(text) to database, the files should have certain fields(columns), but sometime customer give additional fileds. These additional fields should be recorded to table, but the fields name and number are unknown. For ex

TableA stores additional columns, like this, Col1, Col2(or more columns), Col names are unknown. But I get Col1,Col2... value and stored in @Col, by using system Sp and system tables

ID, Col1, Col2
1 some1 some2
2 Null some3
3 some4 Null


TableB is created to store the TableA column info(just for not null info,see example), it should like this

ID ColName ColContent
1 Col1 some1
1 Col2 some2
2 Col2 some3
3 Col3 some4

I think it should use dynamic sql, and query Dynamic SQL in this Forum, but even I know some Dynamic SQL now, I still do't know how to solve this question. Can any one give me some directions. Hope you know what I mean as my English not good.

Thanks in advance

 

[rac2]

Assuming you have data in TableA as you show, putting the data in TableB seems easy enough without dynamic SQL. You know the column names. They are Col1, Col2, and Col3. That is fixed because you have used those names to create TableA.

I am just restating the situation as I understand it. Correct me if necessary.

So, insert rows in TableB with this

INSERT TableB (ID, ColName, ColContent)
SELECT ID, 'Col1', Col1
FROM TableA
WHERE Col1 IS NOT NULL

Repeat for each optional column.

It may be possible to use UNION to get all three columns in one query.

INSERT TableB (ID, ColName, ColContent)
SELECT ID, 'Col1', Col1
FROM TableA
WHERE Col1 IS NOT NULL

UNION

SELECT ID, 'Col2', Col2
FROM TableA
WHERE Col2 IS NOT NULL

UNION

SELECT ID, 'Col3', Col3
FROM TableA
WHERE Col3 IS NOT NULL

Maybe you wish to write something that finds the names of the optional columns? I have no idea how to do that.

I suggest that TableB needs an IDENTITY column too which identifies its own rows. The ID column is a FOREIGN key, TableB should have a PRIMARY key. It is not necessary but it doesnt cost anything and could be handy if your application ever needs to update the table.

 

[znet]

Thanks Rac2,
Maybe I didn't say clearly, the Col name is unknown(col1,col2...), TableA is produced by dynamic SQL, these fields are the 'Wrong' fileds mistakenly added by customer. I got col name by checking system table(and can get number of column), they are saved in @Col. TableB is defined known. Using Dynamic SQL is seem a right direction, but I feel difficult still.

 

[znet]

Hi Rac2,
Using union is right direction, but how to show 'Col'. For example, suppose @Col='Column', I want to using select like this: select 'Column',Column from whatever. How to do? select '@col',@col from ... , not work.

 

[rac2]

When you say "...TableA is produced by dynamic SQL..." do you mean the table is created with CREATE TABLE in a dynamic SQL statement? Or do you mean rows are added to the table with INSERT TableA in a dynamic SQL statement?

How do you find the names of the optional columns? Does the import file have column names in the first row? Do you read that row, then lookup the columns in the database system table then build a temporary table with the necessary columns?


Assume @col has the name of a column from TableA then

DECLARE @strQuery VARCHAR(2000)

SET @strQuery = "INSERT TableB (ID, ColName, ColContent) "
SET @strQuery = @strQuery + "SELECT ID, '" + @col + "', "
SET @strQuery = @strQuery + @col + " "
SET @strQuery = @strQuery + "FROM TableA "
SET @strQuery = @strQuery + "WHERE Col1 IS NOT NULL "


EXECUTE (@strQuery)

Nothing fancy here, just build a string which is the SQL statement to be executed.

 

[rac2]

Sorry those " should be ' and to include the single quote in the string use ''.

So

SET @strQuery = @strQuery + 'SELECT ID, ''' + @col + ''', '

 

[znet]

Thanks rac2,
it works fine