Post reply

Append TO or Update FROM to load new schema with data from existing table?

  • September 8, 2009 at 6:42 pm

    #204355

    I have a table with 4.5 million rows. Finding that the USPS uses 28 char street names, while LA County uses 24 chars, I needed that column wider, and a few other changes. I wrote a select into a new table using a pad function to get the space I needed, and wound up with nvarchars of 8000! (GAH!)

    I tried just changing the schema of an existing table once and had the server go bye-bye for days!

    Ok, now I create a NEW empty table with the schema I need, but what is the syntax to get the data into it? Append from? Update? (obviously I can't select into it)

    I've looked for examples and come up empty.

    Thanks!

  • September 8, 2009 at 6:51 pm

    #1050860

    ironically it's really easy: the INSERT command combined with a SELECT with no WHERE statement will copy it all for you.

    Insert Into NewTable (ColumnList...)

    Select ColumnList...

    From OldTable

    --Order By OldPK --optional order by STATE.County,City,Zipcode?

    alternatively, you could use the GUI to change the table, and it will do everything automatically behind the scenes:

    1: create the new table

    2: create all foreign keys, constraints,defaults

    3: migrate the data

    4. drop the old table

    5: rename the new table to teh proper name

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 8, 2009 at 7:29 pm

    #1050869

    Yeah, INSERT was the command I was searching for!

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

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