combining identical tables with different records into one table

[rpatel18]

I have information for the 50 states split by zip codes and in 50 different tables. I want to combine them all into one table but can't seem to get it to work. I get an error.....8114 error converting data type nvarchar to float. Heres the code..

Insert into All_States
Select *
from [AL IRS]
GO
Insert into All_States
Select *
from [AR IRS]
GO
Insert into All_States
Select *
from [AZ IRS]
GO
Insert into All_States
Select *
from [CA IRS]
GO
Insert into All_States
Select *
from [CO IRS]
GO
Insert into All_States
Select *
from [CT IRS]
GO
Insert into All_States
Select *
from [DC IRS]
GO
Insert into All_States
Select *
from [DE IRS]
GO
Insert into All_States
Select *
from [FL IRS]
GO
Insert into All_States
Select *
from [GA IRS]
GO
Insert into All_States
Select *
from [HI IRS]
GO
Insert into All_States
Select *
from [IA IRS]
GO
Insert into All_States
Select *
from [IL IRS]
GO

 

[LNBruno]

You're getting "error converting data type nvarchar to float" which indicates that you're trying to put string data into a numeric field...

What are the column names and data types of (*) in your select statements?

< M!ke >
Acupuncture Development: a jab well done.

 

[LNBruno]

(hit the send button too quickly)

...compared to the column names and data types in your All_States table?

< M!ke >
Acupuncture Development: a jab well done.

 

[rpatel18]

the column names are all the same and the data types are all the same as well. I just renamed the AK IRS file to All_States.

 

[bborissov]

Or field order in one (or more) doesn't match field order in All_States table.
Your could try to make the INSERT and explicitly show the field list:

INSERT INTO All_States (ZipCode, StreetName ....)
SELECT ZipCode, StreeName ...
       FROM [AL IRS]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rpatel18]

correction, you were right, some of the columns are text instead of numbers....

thanx!

 

[LNBruno]

Are you executing this all at once? If so, try each insert statement separately. It only takes one table to be different to cause the entire script to error out.

< M!ke >
Acupuncture Development: a jab well done.

 

[rpatel18]

well, going through the tables, I found out that many of them if not all of them have different field properties(if it should be text or number based). Is there a way to batch correct this?

 

[LNBruno]

Just FYI:

If your All_States table has zip defined as numeric, you're going to loose the leading zeros on some of them.

< M!ke >
Acupuncture Development: a jab well done.

 

[SQLSister]

Trust me you do not want zip codes stored as float! Float is not an exact datatype. Postal codes should be stored as varchar. If you don;t do calulations on it, don't store it as a number and never use float in any event unless you like rounding errors.

Questions about posting. See faq183-874

 

[LNBruno]

Is there a way to batch correct this?

Check out the CONVERT or CAST functions...

< M!ke >
Acupuncture Development: a jab well done.

 

[rpatel18]

I know that but all of this data is already here. I'm just trying to compile all the data for the this company I work for and its a pretty big mess right now.

 

[rpatel18]

didn't mean to word it that way...should've said "Yes it should be done that way

 

[LNBruno]

I meant use CONVERT or CAST in your INSERTs.

< M!ke >
Acupuncture Development: a jab well done.