Combining multiple identical tables into one

[rpatel18]

I have 51 tables(all 50 states plus DC), all with identical fields. I want to create a table that combines them all into one table but can't seem to figure it out. Heres is a test code that I tried to run but get an error "too many fields"

INSERT INTO [IRS '98]
SELECT a.ZipCode1998, a.NumberofReturns1998, a.TotalExemptions1998, a.DependentExemptions1998, a.AGI1998, a.ReturnsSalariesWages1998, a.AmountSalaryWages1998, a.ReturnsTaxableInterest1998, a.AmountTaxableInterest1998, a.ReturnsEIC1998, a.AmountEIC1998, a.ReturnsTotalTax1998, a.AmountTotalTax1998, a.ReturnsScheduleC1998, a.TotalScheduleC1998, a.ReturnsScheduleF1998, a.TotalScheduleF1998, a.ReturnsScheduleA1998, a.AmountScheduleA1998, a.NumberofReturns1998_U10K, a.TotalExemptions1998_U10K, a.DependentExemptions1998_U10K, a.AGI1998_U10K, a.ReturnsSalariesWages1998_U10K, a.AmountSalaryWages1998_U10K, a.ReturnsTaxableInterest1998_U10K, a.AmountTaxableInterest1998_U10K, a.ReturnsEIC1998_U10K, a.AmountEIC1998_U10K, a.ReturnsTotalTax1998_U10K, a.AmountTotalTax1998_U10K, a.ReturnsScheduleC1998_U10K, a.TotalScheduleC1998_U10K, a.ReturnsScheduleF1998_U10K, a.TotalScheduleF1998_U10K, a.ReturnsScheduleA1998_U10K, a.AmountScheduleA1998_U10K, a.NumberofReturns1998_10KU25K, a.TotalExemptions1998_10KU25K, a.DependentExemptions1998_10KU25K, a.AGI1998_10KU25K, a.ReturnsSalariesWages1998_10KU25K, a.AmountSalaryWages1998_10KU25K, a.ReturnsTaxableInterest1998_10KU25K, a.AmountTaxableInterest1998_10KU25K, a.ReturnsEIC1998_10KU25K, a.AmountEIC1998_10KU25K, a.ReturnsTotalTax1998_10KU25K, a.AmountTotalTax1998_10KU25K, a.ReturnsScheduleC1998_10KU25K, a.TotalScheduleC1998_10KU25K, a.ReturnsScheduleF1998_10KU25K, a.TotalScheduleF1998_10KU25K, a.ReturnsScheduleA1998_10KU25K, a.AmountScheduleA1998_10KU25K, a.NumberofReturns1998_25KU50K, a.TotalExemptions1998_25KU50K, a.DependentExemptions1998_25KU50K, a.AGI1998_25KU50K, a.ReturnsSalariesWages1998_25KU50K, a.AmountSalaryWages1998_25KU50K, a.ReturnsTaxableInterest1998_25KU50K, a.AmountTaxableInterest1998_25KU50K, a.ReturnsEIC1998_25KU50K, a.AmountEIC1998_25KU50K, a.ReturnsTotalTax1998_25KU50K, a.AmountTotalTax1998_25KU50K, a.ReturnsScheduleC1998_25KU50K, a.TotalScheduleC1998_25KU50K, a.ReturnsScheduleF1998_25KU50K, a.TotalScheduleF1998_25KU50K, a.ReturnsScheduleA1998_25KU50K, a.AmountScheduleA1998_25KU50K, a.NumberofReturns1998_50K, a.TotalExemptions1998_50K, a.DependentExemptions1998_50K, a.AGI1998_50K, a.ReturnsSalariesWages1998_50K, a.AmountSalaryWages1998_50K, a.ReturnsTaxableInterest1998_50K, a.AmountTaxableInterest1998_50K, a.ReturnsEIC1998_50K, a.AmountEIC1998_50K, a.ReturnsTotalTax1998_50K, a.AmountTotalTax1998_50K, a.ReturnsScheduleC1998_50K, a.TotalScheduleC1998_50K, a.ReturnsScheduleF1998_50K, a.TotalScheduleF1998_50K, a.ReturnsScheduleA1998_50K, a.AmountScheduleA1998_50K
FROM
(
SELECT ZipCode1998, NumberofReturns1998, TotalExemptions1998, DependentExemptions1998, AGI1998, ReturnsSalariesWages1998, AmountSalaryWages1998, ReturnsTaxableInterest1998, AmountTaxableInterest1998, ReturnsEIC1998, AmountEIC1998, ReturnsTotalTax1998, AmountTotalTax1998, ReturnsScheduleC1998, TotalScheduleC1998, ReturnsScheduleF1998, TotalScheduleF1998, ReturnsScheduleA1998, AmountScheduleA1998, NumberofReturns1998_U10K, TotalExemptions1998_U10K, DependentExemptions1998_U10K, AGI1998_U10K, ReturnsSalariesWages1998_U10K, AmountSalaryWages1998_U10K, ReturnsTaxableInterest1998_U10K, AmountTaxableInterest1998_U10K, ReturnsEIC1998_U10K, AmountEIC1998_U10K, ReturnsTotalTax1998_U10K, AmountTotalTax1998_U10K, ReturnsScheduleC1998_U10K, TotalScheduleC1998_U10K, ReturnsScheduleF1998_U10K, TotalScheduleF1998_U10K, ReturnsScheduleA1998_U10K, AmountScheduleA1998_U10K, NumberofReturns1998_10KU25K, TotalExemptions1998_10KU25K, DependentExemptions1998_10KU25K, AGI1998_10KU25K, ReturnsSalariesWages1998_10KU25K, AmountSalaryWages1998_10KU25K, ReturnsTaxableInterest1998_10KU25K, AmountTaxableInterest1998_10KU25K, ReturnsEIC1998_10KU25K, AmountEIC1998_10KU25K, ReturnsTotalTax1998_10KU25K, AmountTotalTax1998_10KU25K, ReturnsScheduleC1998_10KU25K, TotalScheduleC1998_10KU25K, ReturnsScheduleF1998_10KU25K, TotalScheduleF1998_10KU25K, ReturnsScheduleA1998_10KU25K, AmountScheduleA1998_10KU25K, NumberofReturns1998_25KU50K, TotalExemptions1998_25KU50K, DependentExemptions1998_25KU50K, AGI1998_25KU50K, ReturnsSalariesWages1998_25KU50K, AmountSalaryWages1998_25KU50K, ReturnsTaxableInterest1998_25KU50K, AmountTaxableInterest1998_25KU50K, ReturnsEIC1998_25KU50K, AmountEIC1998_25KU50K, ReturnsTotalTax1998_25KU50K, AmountTotalTax1998_25KU50K, ReturnsScheduleC1998_25KU50K, TotalScheduleC1998_25KU50K, ReturnsScheduleF1998_25KU50K, TotalScheduleF1998_25KU50K, ReturnsScheduleA1998_25KU50K, AmountScheduleA1998_25KU50K, NumberofReturns1998_50K, TotalExemptions1998_50K, DependentExemptions1998_50K, AGI1998_50K, ReturnsSalariesWages1998_50K, AmountSalaryWages1998_50K, ReturnsTaxableInterest1998_50K, AmountTaxableInterest1998_50K, ReturnsEIC1998_50K, AmountEIC1998_50K, ReturnsTotalTax1998_50K, AmountTotalTax1998_50K, ReturnsScheduleC1998_50K, TotalScheduleC1998_50K, ReturnsScheduleF1998_50K, TotalScheduleF1998_50K, ReturnsScheduleA1998_50K, AmountScheduleA1998_50K
FROM [AK IRS] UNION
SELECT ZipCode1998, NumberofReturns1998, TotalExemptions1998, DependentExemptions1998, AGI1998, ReturnsSalariesWages1998, AmountSalaryWages1998, ReturnsTaxableInterest1998, AmountTaxableInterest1998, ReturnsEIC1998, AmountEIC1998, ReturnsTotalTax1998, AmountTotalTax1998, ReturnsScheduleC1998, TotalScheduleC1998, ReturnsScheduleF1998, TotalScheduleF1998, ReturnsScheduleA1998, AmountScheduleA1998, NumberofReturns1998_U10K, TotalExemptions1998_U10K, DependentExemptions1998_U10K, AGI1998_U10K, ReturnsSalariesWages1998_U10K, AmountSalaryWages1998_U10K, ReturnsTaxableInterest1998_U10K, AmountTaxableInterest1998_U10K, ReturnsEIC1998_U10K, AmountEIC1998_U10K, ReturnsTotalTax1998_U10K, AmountTotalTax1998_U10K, ReturnsScheduleC1998_U10K, TotalScheduleC1998_U10K, ReturnsScheduleF1998_U10K, TotalScheduleF1998_U10K, ReturnsScheduleA1998_U10K, AmountScheduleA1998_U10K, NumberofReturns1998_10KU25K, TotalExemptions1998_10KU25K, DependentExemptions1998_10KU25K, AGI1998_10KU25K, ReturnsSalariesWages1998_10KU25K, AmountSalaryWages1998_10KU25K, ReturnsTaxableInterest1998_10KU25K, AmountTaxableInterest1998_10KU25K, ReturnsEIC1998_10KU25K, AmountEIC1998_10KU25K, ReturnsTotalTax1998_10KU25K, AmountTotalTax1998_10KU25K, ReturnsScheduleC1998_10KU25K, TotalScheduleC1998_10KU25K, ReturnsScheduleF1998_10KU25K, TotalScheduleF1998_10KU25K, ReturnsScheduleA1998_10KU25K, AmountScheduleA1998_10KU25K, NumberofReturns1998_25KU50K, TotalExemptions1998_25KU50K, DependentExemptions1998_25KU50K, AGI1998_25KU50K, ReturnsSalariesWages1998_25KU50K, AmountSalaryWages1998_25KU50K, ReturnsTaxableInterest1998_25KU50K, AmountTaxableInterest1998_25KU50K, ReturnsEIC1998_25KU50K, AmountEIC1998_25KU50K, ReturnsTotalTax1998_25KU50K, AmountTotalTax1998_25KU50K, ReturnsScheduleC1998_25KU50K, TotalScheduleC1998_25KU50K, ReturnsScheduleF1998_25KU50K, TotalScheduleF1998_25KU50K, ReturnsScheduleA1998_25KU50K, AmountScheduleA1998_25KU50K, NumberofReturns1998_50K, TotalExemptions1998_50K, DependentExemptions1998_50K, AGI1998_50K, ReturnsSalariesWages1998_50K, AmountSalaryWages1998_50K, ReturnsTaxableInterest1998_50K, AmountTaxableInterest1998_50K, ReturnsEIC1998_50K, AmountEIC1998_50K, ReturnsTotalTax1998_50K, AmountTotalTax1998_50K, ReturnsScheduleC1998_50K, TotalScheduleC1998_50K, ReturnsScheduleF1998_50K, TotalScheduleF1998_50K, ReturnsScheduleA1998_50K, AmountScheduleA1998_50K
FROM [AL IRS] UNION
SELECT ZipCode1998, NumberofReturns1998, TotalExemptions1998, DependentExemptions1998, AGI1998, ReturnsSalariesWages1998, AmountSalaryWages1998, ReturnsTaxableInterest1998, AmountTaxableInterest1998, ReturnsEIC1998, AmountEIC1998, ReturnsTotalTax1998, AmountTotalTax1998, ReturnsScheduleC1998, TotalScheduleC1998, ReturnsScheduleF1998, TotalScheduleF1998, ReturnsScheduleA1998, AmountScheduleA1998, NumberofReturns1998_U10K, TotalExemptions1998_U10K, DependentExemptions1998_U10K, AGI1998_U10K, ReturnsSalariesWages1998_U10K, AmountSalaryWages1998_U10K, ReturnsTaxableInterest1998_U10K, AmountTaxableInterest1998_U10K, ReturnsEIC1998_U10K, AmountEIC1998_U10K, ReturnsTotalTax1998_U10K, AmountTotalTax1998_U10K, ReturnsScheduleC1998_U10K, TotalScheduleC1998_U10K, ReturnsScheduleF1998_U10K, TotalScheduleF1998_U10K, ReturnsScheduleA1998_U10K, AmountScheduleA1998_U10K, NumberofReturns1998_10KU25K, TotalExemptions1998_10KU25K, DependentExemptions1998_10KU25K, AGI1998_10KU25K, ReturnsSalariesWages1998_10KU25K, AmountSalaryWages1998_10KU25K, ReturnsTaxableInterest1998_10KU25K, AmountTaxableInterest1998_10KU25K, ReturnsEIC1998_10KU25K, AmountEIC1998_10KU25K, ReturnsTotalTax1998_10KU25K, AmountTotalTax1998_10KU25K, ReturnsScheduleC1998_10KU25K, TotalScheduleC1998_10KU25K, ReturnsScheduleF1998_10KU25K, TotalScheduleF1998_10KU25K, ReturnsScheduleA1998_10KU25K, AmountScheduleA1998_10KU25K, NumberofReturns1998_25KU50K, TotalExemptions1998_25KU50K, DependentExemptions1998_25KU50K, AGI1998_25KU50K, ReturnsSalariesWages1998_25KU50K, AmountSalaryWages1998_25KU50K, ReturnsTaxableInterest1998_25KU50K, AmountTaxableInterest1998_25KU50K, ReturnsEIC1998_25KU50K, AmountEIC1998_25KU50K, ReturnsTotalTax1998_25KU50K, AmountTotalTax1998_25KU50K, ReturnsScheduleC1998_25KU50K, TotalScheduleC1998_25KU50K, ReturnsScheduleF1998_25KU50K, TotalScheduleF1998_25KU50K, ReturnsScheduleA1998_25KU50K, AmountScheduleA1998_25KU50K, NumberofReturns1998_50K, TotalExemptions1998_50K, DependentExemptions1998_50K, AGI1998_50K, ReturnsSalariesWages1998_50K, AmountSalaryWages1998_50K, ReturnsTaxableInterest1998_50K, AmountTaxableInterest1998_50K, ReturnsEIC1998_50K, AmountEIC1998_50K, ReturnsTotalTax1998_50K, AmountTotalTax1998_50K, ReturnsScheduleC1998_50K, TotalScheduleC1998_50K, ReturnsScheduleF1998_50K, TotalScheduleF1998_50K, ReturnsScheduleA1998_50K, AmountScheduleA1998_50K
FROM [CA IRS] UNION
SELECT ZipCode1998, NumberofReturns1998, TotalExemptions1998, DependentExemptions1998, AGI1998, ReturnsSalariesWages1998, AmountSalaryWages1998, ReturnsTaxableInterest1998, AmountTaxableInterest1998, ReturnsEIC1998, AmountEIC1998, ReturnsTotalTax1998, AmountTotalTax1998, ReturnsScheduleC1998, TotalScheduleC1998, ReturnsScheduleF1998, TotalScheduleF1998, ReturnsScheduleA1998, AmountScheduleA1998, NumberofReturns1998_U10K, TotalExemptions1998_U10K, DependentExemptions1998_U10K, AGI1998_U10K, ReturnsSalariesWages1998_U10K, AmountSalaryWages1998_U10K, ReturnsTaxableInterest1998_U10K, AmountTaxableInterest1998_U10K, ReturnsEIC1998_U10K, AmountEIC1998_U10K, ReturnsTotalTax1998_U10K, AmountTotalTax1998_U10K, ReturnsScheduleC1998_U10K, TotalScheduleC1998_U10K, ReturnsScheduleF1998_U10K, TotalScheduleF1998_U10K, ReturnsScheduleA1998_U10K, AmountScheduleA1998_U10K, NumberofReturns1998_10KU25K, TotalExemptions1998_10KU25K, DependentExemptions1998_10KU25K, AGI1998_10KU25K, ReturnsSalariesWages1998_10KU25K, AmountSalaryWages1998_10KU25K, ReturnsTaxableInterest1998_10KU25K, AmountTaxableInterest1998_10KU25K, ReturnsEIC1998_10KU25K, AmountEIC1998_10KU25K, ReturnsTotalTax1998_10KU25K, AmountTotalTax1998_10KU25K, ReturnsScheduleC1998_10KU25K, TotalScheduleC1998_10KU25K, ReturnsScheduleF1998_10KU25K, TotalScheduleF1998_10KU25K, ReturnsScheduleA1998_10KU25K, AmountScheduleA1998_10KU25K, NumberofReturns1998_25KU50K, TotalExemptions1998_25KU50K, DependentExemptions1998_25KU50K, AGI1998_25KU50K, ReturnsSalariesWages1998_25KU50K, AmountSalaryWages1998_25KU50K, ReturnsTaxableInterest1998_25KU50K, AmountTaxableInterest1998_25KU50K, ReturnsEIC1998_25KU50K, AmountEIC1998_25KU50K, ReturnsTotalTax1998_25KU50K, AmountTotalTax1998_25KU50K, ReturnsScheduleC1998_25KU50K, TotalScheduleC1998_25KU50K, ReturnsScheduleF1998_25KU50K, TotalScheduleF1998_25KU50K, ReturnsScheduleA1998_25KU50K, AmountScheduleA1998_25KU50K, NumberofReturns1998_50K, TotalExemptions1998_50K, DependentExemptions1998_50K, AGI1998_50K, ReturnsSalariesWages1998_50K, AmountSalaryWages1998_50K, ReturnsTaxableInterest1998_50K, AmountTaxableInterest1998_50K, ReturnsEIC1998_50K, AmountEIC1998_50K, ReturnsTotalTax1998_50K, AmountTotalTax1998_50K, ReturnsScheduleC1998_50K, TotalScheduleC1998_50K, ReturnsScheduleF1998_50K, TotalScheduleF1998_50K, ReturnsScheduleA1998_50K, AmountScheduleA1998_50K
FROM [CO IRS]
) a

 

[Crowley16]

what about something like:

insert into MainTable
select * from tableA
union select * from tableB
...

make sure all tables have columns in the same order, of the same datatype...

--------------------
Procrastinate Now!

 

[rpatel18]

I get a "syntax error in FROM clause" with this test code,

INSERT INTO [IRS '98]
SELECT * FROM [CA IRS],
UNION SELECT * FROM [OH IRS],
UNION SELECT * FROM [CO IRS],
UNION SELECT * FROM [PA IRS],
UNION SELECT * FROM [ID IRS];

 

[Crowley16]

no commas

also, try wrapping brackets around your select, i.e.

insert into...
(
select * from ...
union select * from ...
...
)

--------------------
Procrastinate Now!

 

[rpatel18]

Syntax error in INSERT INTO statement

INSERT INTO [IRS '98]
( SELECT * FROM [CA IRS]
UNION SELECT * FROM [OH IRS]
UNION SELECT * FROM [CO IRS]
UNION SELECT * FROM [PA IRS]
UNION SELECT * FROM [ID IRS] )

 

[Remou]

How about:

[tt]INSERT INTO [IRS '98]
Select * From
(
SELECT * FROM [CA IRS]
UNION SELECT * FROM [OH IRS]
UNION SELECT * FROM [CO IRS]
UNION SELECT * FROM [PA IRS]
UNION SELECT * FROM [ID IRS]
)[/tt]

 

[rpatel18]

too many fields defined now, this is weird.

 

[Remou]

It may be safest to set this up in VBA and simply change the SQL string to each state, especially if this is a once-off thing.

 

[rpatel18]

well, the tables are set up exactly the same, same fields, I dont want to go through all 50 states and copy all the rows of records.

 

[Remou]

You can use VBA.

strState="CA"
strSQL="INSERT INTO [IRS '98] SELECT * FROM [" & strState & " IRS]"
currentdb.execute strSQL, dbFailOnerror

You can use a table of states to provide strState.

 

[TheAceMan1]

How are ya rpatel18 . . .

As an alternative I'm thinking a form with a single column [blue]multiselect listbox[/blue] showing all the tables and a [blue]command button[/blue].

The [blue]rowsource[/blue] of the listbox would be:

[blue]SELECT Name FROM MSysObjects WHERE ((Type=1) OR (Type=6));[/blue]

And the button code would be:

[blue]   Dim itm, lbx As ListBox, SQL As String
   
   Set lbx = Me!ListboxName
   
   For Each itm In lbx.ItemsSelected
      SQL = "INSERT INTO [IRS '98] " & _
            "SELECT * " & _
            "FROM [" & lbx.ItemData(itm) & "];"
      CurrentDb.Execute SQL, dbFailOnError
   Next
   
   Set lbx = Nothing[/blue]

You'll have to multiselect 50 tables but I believe you can live with that . . .

[blue]Your Thoughts? . . .[/blue]

BTW: [blue]if only the state table names end with IRS[/blue] then looping a recordset would get rid of the form . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

If this is a once-off update, there is no need for a form, it can be run in a scratch module. A table or array of states can be used, if that is more convenient, but it may take as much time to build such a table or array as it would to type in the two characters.

 

[TheAceMan1]

Howdy Remou . . .

The form is only invoked by the listbox which [blue]prevents having to enter all the states![/blue] Not having to enter 50 items (two characters or not) [blue]has to be appealing![/blue] The form is easy, uncomplicated (two objects), has a minimum amount of code, is very short on design time and gets the job done!

Since we don't know if there are other tables besides the states ending in [blue]IRS[/blue] then looping in code could be a problem.

Albeit this secnario is the eqivalent of a one time affair but [blue]there's no law against making things easier for oneself![/blue] I personally would use the form and be done with it! Many times I've had to do so.

All in all there's one thing for sure . . . [blue]the forms not gonna hurt anything![/blue] [purple]If we started at the same time I'd finish before you . . .[/purple] and I'd keep the form in a library just for reference as you never know.

So . . . maybe you would'nt but I would!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

I would have thought that it would be easier to simply run through an array, rather than go to the trouble of creating a form and listbox to hold the array, especially as this problem was originally posted as an SQL server problem. But each to their own.

 

[TheAceMan1]

[blue] . . . especially as this problem was originally posted as an SQL server problem.[/blue]

Not quite sure of your point espcially since there's no mention of [blue]SQL Server[/blue] in this thread.

So what happen to [blue]rpatel18[/blue]? Some feedback here is needed . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[rpatel18]

I'm going to try and normalize the data in excel(came in excel format), take out all the none numerical values since there should only be numbers in there(many fields have $,-, and *) and then try to re-import them. I think the issue is I can't combine them because some fields are text fields in some tables and in other tables they are number fields. I think thats what seems to be the issue.

 

[TheAceMan1]

Roger That rpatel18 . . .

BTW: are you talking [blue]SQL Server?[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[rpatel18]

well, I have it in excel, and import it to access(more comfortable working in access), and then it'll get imported to sql server.

 

[PHV]

I wonder you couldn't do it all once with DTS ?

 

[rpatel18]

DTS?