Post reply

Copying Tables

  • March 24, 2008 at 9:51 pm

    #180346

    Hi,

    I am a new user to SQL Server Express 2005.

    I need to split a table in my database (into 2 tables). I was going to copy the table and delete information from both, but cannot figure out how to do so. I can copy the structure but not both structure and data.

    Thanks

    James

  • March 26, 2008 at 2:49 am

    #793894

    The following query will copy the table structure and all of the data

    SELECT * INTO NewTable FROM OldTable

    If you only want to copy some data from the old table, you can add a WHERE clause ie

    SELECT * INTO NewTable FROM OldTable

    WHERE FieldName = 'whatever'

    Hope this helps

    Graham

  • March 26, 2008 at 3:09 am

    #793899

    Hi

    "Select * into" does not create the indexes from the source table. Ex:- Select * into table1 from table2 . table1 will be created with the same columns as that of table2 but the indexes,Defaults,Fkeys on table2 will not be created.

    You are better off creating the tables with a script and then inserting the relevant data in to the tables. Then you can delete the data from the original table. If you are splitting the table in such way that some columns from the original table are put in the new table , then after inserting the data you can drop the relevant columns from the original table.

    "Keep Trying"

  • March 26, 2008 at 4:55 pm

    #794397

    Hi,

    Thanks for your replies. In relation to creating the structure (either with script or copying from the other table) and then inserting the data, I have had difficulty inserting multiple records.

    To make the situation easier, I have figured out to create an Access Project ADP and link it to the backend on SQL Express, which gives me the option to copy the table.

    Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

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