Post reply

Insert into Table from Table

  • February 26, 2009 at 3:44 pm

    #384031

    I have little to no SQL experience so bear with me on this.

    Basically I have some software that exports into a table in Microsoft SQL. What I want to do is take all the data thats exported into that table and insert it into another table then and after it inserts it into another table it deletes all the data from the first able.

  • February 26, 2009 at 3:50 pm

    #950876

    hi,

    Here is an example you can hopefully build a script from. just change the table names and columns.

    insert names2 (fname, lname) select fname, lname from names

    truncate table names

    You will need to provide your table structure if you want exact syntax.

    Bevan

  • February 27, 2009 at 7:22 am

    #951156

    Thanks for the quick response.

    For the most part this is just a proof of concept so as long as I can show this working it can be expanded on.

    Basically this is what happens. The software exports a single(it will be more in the future) line of data into a table called 'test' and then the software triggers a .bat file to execute a sql script which looks like this.

    insert test2 (data) select data from test

    truncate table test

    So I took your line and modified it to what I need the problem is I need it to update 'test2' and not insert into 'test2'. I want 'test2' to have the most updated information and with insert it just keeps adding.

    Is there a way to update another table that will at first have no data in it? Would I have to build 'test2' to be exactly like 'test'?

  • February 27, 2009 at 9:36 pm

    #951486

    Google "UPSERT SQL SERVER 2005".

    Basically, you need both an UPDATE and an INSERT. Both would have joins between the two tables. For the UPDATE, if the join succeeds, update the target table from the source table. For the INSERT, if the join fails, then do the insert. The "EXCEPT" union statement may work well for that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 20, 2011 at 3:57 pm

    #1276295

    How to avoid duplicates on this case

  • January 20, 2011 at 4:00 pm

    #1276299

    Add a distinct to your select:

    insert names2 (fname, lname) select distinct fname, lname from names

    truncate table names

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply