create table and populate

Question

Post reply

create table and populate

smknox

Hall of Fame

Points: 3275
More actions
September 3, 2005 at 9:44 am

#168591

I want to split a table in an existing DB into two tables. I know how to write script to create a new table, and another script to select all records, but what's the syntax to select all records from one table and use that select statement to make a new table?
THanks 🙂
Sam

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

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

is this what you're looking for ?!

CREATE TABLE NewTable

(colOne int,

colTwo varchar(50))

INSERT INTO NewTable

SELECT colOne, colTwo

FROM OldTable

**ASCII stupid question, get a stupid ANSI !!!**

smknox Hall of Fame Points: 3275 More actions · Answer 2

As simple as it looks, yes, that's all I needed. I hadn't done that before and was having a brain freeze. Appreciate it!

Sam

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

I think what you are looking for is SELECT/INTO... it faster than any other method especially if you turn SELECT INTO/BULK COPY on...

It creates the table on the fly fut won't create the Primary Key... don't forget to add one...

SELECT colOne, colTwo

INTO NewTable

FROM OldTable

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Might I point out that you'll want to also copy the primary key to the second table, most likely making it the clustered index, also I'd add the relation with on cascade delete.

smknox Hall of Fame Points: 3275 More actions · Answer 5

THanks for the help. Will be working on this today!

Sam