append 3 files with same structure & add filename

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

append 3 files with same structure & add filename

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-13 : 23:18:36

Hoping someone can help. I am fairly new to sql 2000 and need to write a sql query which will combine 3 tables (i.e. jan, feb, mar) with the same exact file structure (i.e. customer, address, city, state,zip) into one table (i.e. combined), but before I combine the three tables, I need to add a new field to the structure which will house the table name. Thus, my combined file structure would have filename, customer, address, city, state, zip.

Is this possible? Can someone show me how to write this query? Thank you in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 23:21:20

[code]
INSERT INTO [COMBINE] (filename, customer, address, city, state, zip)
SELECT 'jan', customer, address, city, state, zip
FROM jan
UNION ALL
SELECT 'feb', customer, address, city, state, zip
FROM feb
UNION ALL
SELECT 'mar', customer, address, city, state, zip
FROM mar
[/code]

KH
[spoiler]Time is always against us[/spoiler]