INSERT INTO SELECT * FROM tblName IN 'Db Location'
Results 1 to 8 of 8

Thread: INSERT INTO SELECT * FROM tblName IN 'Db Location'
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Location
    Tanzania
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post INSERT INTO SELECT * FROM tblName IN 'Db Location'

    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?

  2. #2
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,619
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    Instead of using Select *, name your fields

  3. #3
    New Member
    Join Date
    Aug 2015
    Location
    Tanzania
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    Quote Originally Posted by Kyle123 View Post
    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...

  4. #4
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,619
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    There isn't...

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    425
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    use a copy of the database and delete the field.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,425
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    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?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Aug 2015
    Location
    Tanzania
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    Quote Originally Posted by xenou View Post
    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'
    Last edited by mathematician; Jun 8th, 2019 at 10:28 AM.

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,425
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INSERT INTO SELECT * FROM tblName IN 'Db Location'

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •