Post reply

move index

  • June 2, 2008 at 10:38 pm

    #207993

    Am moving the table from one data base to another by using the select * into newdb..tablename from olddb..tablename ,that time index not creating only table structure creating but want move the index too .

    is there any possible to move index ?

  • June 2, 2008 at 11:53 pm

    #823406

    Hi sudhakara,

    Can you elobrate why do need to move Index? What is problem with Create Index later on table on newdb? I am hoping that you are running query under context of Newdb.

    Regards
    Shrikant Kulkarni

  • June 3, 2008 at 12:08 am

    #823412

    reason is there are somany tables so its taking lot of time to generate script and applying geneated script on the data base .

  • June 3, 2008 at 12:11 am

    #823416

    Select ... into only creates and populates the table.

    If you want indexes, constraints, primary key, triggers or anything else from the original table, you'll have to script them from the source database and apply them on the new database

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2008 at 12:17 am

    #823420

    is there any spceific reason for not creating constraint while using the select * into ...

  • June 3, 2008 at 12:27 am

    #823424

    Select into isn't limited to coming from a single table. Imagine something like this (example from BOL)

    SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS Province, a.PostalCode

    INTO dbo.EmployeeAddresses

    FROM Person.Contact AS c

    JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID

    JOIN HumanResources.EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID

    JOIN Person.Address AS a on a.AddressID = ea.AddressID

    JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID

    None of the the pk constraints on the base tables involved in that are guarenteed to work on the new table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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