Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: T-SQL to split data from one table into two tables?

    What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

    Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

    Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.

    Code:
    -- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
    CREATE TABLE OriginalSingleTableRecords (
    	ID INT IDENTITY (1, 1) NOT NULL,
    
    	ColumnA VARCHAR(100) NOT NULL,
    	ColumnB VARCHAR(100) NOT NULL,
    
    	CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
    )
    
    CREATE TABLE TargetParentRecords (
    	ParentID INT IDENTITY (1, 1) NOT NULL,
    
    	ColumnA VARCHAR(100) NOT NULL,
    
    	CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
    )
    
    -- Each row in this table must link to a TargetParentRecords row
    CREATE TABLE TargetChildRecords (
    	ID INT IDENTITY (1, 1) NOT NULL,
    
    	ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
    	ColumnB VARCHAR(100) NOT NULL,
    
    	CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
    )

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Also is there any way to move the two table data from one server and append to tables on another server. The second server will already have data there so I must do an actual append.

    The only complexity is the IDs. It doesn't matter what they are, except the link between parent/child records must be preserved.

    Again, this can be easily (but slowly) done via cursor. What's the best route?

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Make sure the constraints are dropped before you do this:

    if object_id('dbo.TargetParentRecords') is not null drop table dbo.TargetParentRecords
    go
    if object_id('dbo.TargetChildRecords') is not null drop table dbo.TargetChildRecords
    go
    select ParentID=ID, ColumnA into dbo.TargetParentRecords from dbo.OriginalSingleTableRecords order by ID
    go
    select t.ParentID, o.ColumnB into dbo.TargetChildRecords
    from dbo.OriginalSingleTableRecords o inner join dbo.TargetParentRecords t
    on o.ID = t.ParentID
    order by t.ParentID, o.ColumnB
    go
    alter table dbo.TargetChildRecords add ID identity(1,1) not null
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    I see; basically preserve the IDs as-is across the table change. That works well if the target table is blank. If the target table isn't blank, you can adjust the code to use a fixed ID offset assuming you don't have to worry about race conditions.

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    Thank you. That response helped.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, if you are appending, than avoid just straight INSERT, because it will be MUCH slower vs. SELECT...INTO due to the nature of the latter. When appending create a view (by using the SELECT...INTO as a template) and do BCP...OUT/BULK INSERT using appropriate switches.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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