INSERT INTO SELECT * FROM tblName IN 'Db Location'

[mathematician]

Hi,
I'm trying to consolidate data from multiple access databases into SQL Server. The code works fine but the only drawback is when there is a new column in a source access database table 'Db Location' it results into an error. Is there a way in SQL where I can specify to SKIP unmatched fields?

 

[Kyle123]

Instead of using Select *, name your fields

 

[mathematician]

Instead of using Select *, name your fields

Thank you @Kyle123. The problem with that is I have many columns and many tables so I'm looking for a simpler solution...

 

[Kyle123]

There isn't...

 

[stumac]

use a copy of the database and delete the field.

 

[xenou]

can you give an example of what you mean by an unmatched field? Is this in your Select clause? Or your Where clause or Join clause?

 

[mathematician]

can you give an example of what you mean by an unmatched field? Is this in your Select clause? Or your Where clause or Join clause?

I mean some columns that are in FROM table but are not in INTO table. I want to ignore them and continue appending records. As it stands it gives an error when there is any column in the FROM table that do not exist in INTO table. My sql statement is

INSERT INTO tblINTO SELECT * FROM tblFROM IN 'tblFROM_DB_LOCATION'

 

[xenou]

There is probably no simple was to do this with plain SQL. I suppose there are tools that would allow it (such as SSIS import export mappings or who knows what else might be available these days). I could imagine ways to do this with vba but building out code like that would probably take longer than just fiddling with the tables and the SQL as you go.