Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Cool Unanswered: Creating new table from existing table

    Hi,

    I m new to this forum with a Query

    Create table <table name1 > as select * from <table name2>

    this works for oracle. do anybody knows its alternative in sqlserver 2000

    thanx.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you can use SELECT INTO

    select * into <table name2> from <table name1 >

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A minor adjustment:
    Code:
    SELECT TOP 0 * INTO <table name2> 
    FROM <table name1 >
     
    SELECT * INTO <table name2> 
    FROM <table name1 >
    This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by pootle flump
    Hi

    A minor adjustment:
    Code:
    SELECT TOP 0 * INTO <table name2> 
    FROM <table name1 >
     
    SELECT * INTO <table name2> 
    FROM <table name1 >
    This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

    HTH
    Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.
    Code:
    SELECT TOP 0 * INTO <table name2> 
    FROM <table name1 >
     
    insert into <table name2> 
    select * 
    from <table name1 >
    Last edited by mallier; 04-20-06 at 05:09.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mallier
    Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.
    Code:
    SELECT TOP 0 * INTO <table name2> 
    FROM <table name1 >
     
    insert into <table name2> 
    select * 
    from <table name1 >
    Duh - yeah - thanks Mallier - copy and paste error
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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