如何从具有不同结构的多个表中提取一个平面文件? [英] How to extract from multiple tables with different structure to one flat file?

问题描述

我有以下三个表，可以使用公用列，键将它们连接起来，并且它们具有不同的列数.

I have following three tables, which can be joined using the common column, key and they have different number of columns.

table1 (key, colnA-1, colnA-2, …, colnA-n)
table2 (key, colnB-1, colnB-2, …, colnB-m)
table3 (key, colnC-1, colnC-2, …, colnC-i)

我需要合并这三个表并导出到如下所示的平面文件

I need to combine the three tables and export to a flat file like following

colnA-1, colnA-2, A, colnA-3, …, colnA-n
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
…
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
…

A means the data in rest of the line are from table1, B means the data in the rest of the line are from table2, etc.  

我的第一个问题是是否可以编写查询以将三个表连接起来以生成具有上述结构的最终表，然后可以使用SSIS将数据从最终表导出到平面文件中?我自己的答案可能不是，因为表A中的记录与表B中的记录具有不同的结构.

My first question is that is it possible to write a query to join the three tables to generate a final table with the structure like above, then I could use SSIS to export the data to a flat file from the final table? My own answer to this is maybe not, since the record from table A has different structure from the record from table B.

另一个问题是，由于我刚开始创建SSIS包，所以我使用SSIS的唯一经验是在SSMS中进行导入/导出，任何人都可以分享一些想法来创建SSIS包以实现我的目标吗?

Another question is, since I am fairly new to create a SSIS package, my only experience using SSIS is to do import/export in SSMS, can anybody share some idea how to create an SSIS package to achieve my goal?

推荐答案

我不太了解您如何将这三个表结合在一起.我需要更精确的细节.

I don't really understand how you're joining the three tables. I'd need specifics to be more precise.

基本思想是

SELECT colnA-1, colnA-2, 'A', …, colnA-n
FROM table1
UNION ALL
SELECT colnB-1, colnB-2, 'B', …, colnB-n
FROM table2
UNION ALL
SELECT colnC-1, colnC-2, 'C', …, colnC-n
FROM table3

如果数据类型不兼容，则需要使用CAST()语句使其兼容.

If your datatypes are incompatible, you'll need to use CAST() statements to make them compatible.

在构建软件包并选择数据源时，请选择一个OLE DB源.在配置窗口中，而不是选择表，而是选择一个SQL命令.然后将您的工作查询粘贴到SSIS中.

When you're building the package and selecting a data source, choose an OLE DB Source. In the configuration window, instead of picking a table, choose an SQL command. Then paste your working query into SSIS.

这篇关于如何从具有不同结构的多个表中提取一个平面文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！