Need to Add Identifier to Concatenate Data Load

[asmithids]

Hello,

I am merging multiple SQL tables that are the exact same using the Concatenate statement in the load script below.  Each ODBC connection represents a separate retail store database that uses the number of the retail store as the database name (i.e. 022, 071, 073, etc...).  The issue is the SQL payment table in the load script does not have a field for the retail store number.  I need to be able to filter by each retail store in the application. 

How can I insert a field that populates the respective retail store number for each table loaded?

Thank you in advance for any assistance with this issue. 

ODBC CONNECT TO [MVE-022] (XUserId is JUVbfZFMQB, XPassword is AXXKUGZOFDOOTbAHYRMODaUGWJSB);
SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-071] (XUserId is YXYGeZFMRA, XPassword is GKNOWGZOFDOOTbAHYRMODaUGWBKA);
Concatenate  SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-073] (XUserId is IYAHaZFMCA, XPassword is YQPTaGZOFDOOTbAHYRMODaUGWJRB);
Concatenate SQL SELECT * FROM "073".dbo.payments;

ODBC CONNECT TO [MVE-118] (XUserId is dZBFdZFMKD, XPassword is RMaXZGZOFDOOTbAHYRMODaUGWZWA);
Concatenate SQL SELECT * FROM "118".dbo.payments;

ODBC CONNECT TO [MVE-119] (XUserId is OKCLRZFMAG, XPassword is SPAXaGZOFDOOTbAHYRMODaUGWBZA);
Concatenate SQL SELECT * FROM "119".dbo.payments;

ODBC CONNECT TO [MVE-169] (XUserId is DaPWXZFMRG, XPassword is KSKCUGZOFDOOTbAHYRMODaUGWZeB);
Concatenate SQL SELECT * FROM "169".dbo.payments;

ODBC CONNECT TO [MVE-170] (XUserId is DWHIcZFMZD, XPassword is DTEOXGZOFDOOTbAHYRMODaUGWRMA);
Concatenate SQL SELECT * FROM "170".dbo.payments;

ODBC CONNECT TO [MVE-233] (XUserId is JLKVcZFMQF, XPassword is VHHSaGZOFDOOTbAHYRMODaUGWBAA);
Concatenate SQL SELECT * FROM "233".dbo.payments;

ODBC CONNECT TO [MVE-335] (XUserId is KdKZUZFMVD, XPassword is eeMAVGZOFDOOTbAHYRMODaUGWZJB);
Concatenate SQL SELECT * FROM "335".dbo.payments;

ODBC CONNECT TO [MVE-418] (XUserId is dFPYeZFMZF, XPassword is WZbZSGZOFDOOTbAHYRMODaUGWZFB);
Concatenate SQL SELECT * FROM "418".dbo.payments;

[Anonymous]

Use a Preceding load on each concatenate.

[asmithids]

Bill,

Not sure of that syntax.  Can you provide? 

Thank you,

[maxgro]

you can add a field in sql

SQL SELECT '022' as NewField, p.* FROM "022".dbo.payments p;

or add a field in the load

load *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

[asmithids]

Hi Massimo,

I added the preceding script to the load script.  Is the syntax correct?  Thank you,

ODBC CONNECT TO [MVE-022] (XUserId is JUVbfZFMQB, XPassword isAXXKUGZOFDOOTbAHYRMODaUGWJSB);

LOAD *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-071] (XUserId is YXYGeZFMRA, XPassword isGKNOWGZOFDOOTbAHYRMODaUGWBKA);

LOAD *, '071' as NewField;

Concatenate  SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-073] (XUserId is IYAHaZFMCA, XPassword isYQPTaGZOFDOOTbAHYRMODaUGWJRB);

LOAD *, '073' as NewField;

Concatenate SQL SELECT * FROM "073".dbo.payments;

[maxgro · Accepted solution]

if all your table have the same number and name of fields, remove concatenate and you should get one table

if they have different fields name and/or numbers, force a concatenate

ODBC CONNECT TO [MVE-22] (XUserId is JUVbfZFMQB, XPasswordisAXXKUGZOFDOOTbAHYRMODaUGWJSB);

table:

LOAD *, '022' as NewField;

SQL SELECT * FROM "022".dbo.payments;

ODBC CONNECT TO [MVE-71] (XUserId is YXYGeZFMRA, XPasswordisGKNOWGZOFDOOTbAHYRMODaUGWBKA);

concatenate (table)

LOAD *, '071' as NewField;

SQL SELECT * FROM "071".dbo.payments;

ODBC CONNECT TO [MVE-73] (XUserId is IYAHaZFMCA, XPasswordisYQPTaGZOFDOOTbAHYRMODaUGWJRB);

concatenate (table)

LOAD *, '073' as NewField;

SQL SELECT * FROM "073".dbo.payments;

[asmithids]

Thank you!!