Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Moving Data Between Tables

Moving Data Between Tables

Scheduled Pinned Locked Moved Database
questionhelp
12 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    Meysam Mahfouzi
    wrote on last edited by
    #1

    I have got a table say something like this:

    ID VARCHAR(30),
    ParentID VARCHAR(30),
    TITLE NVARCHAR(100)

    As it seems, this table has a parent-child relation with itself. the ID column is not auto-generated and is a combination of ASCII characters and numbers with length of 30. Now, I want to move all the data contained in the above table to the following table:

    ID SMALLINT
    ParentID INT
    TITLE NVARCHAR(100)

    In this table, ID is an auto-generated integer Identity column starting from 1. What is the best way for converting the ID columns while moving them to the new table? Thanks in advance for any help :)

    W P 2 Replies Last reply
    0
    • M Meysam Mahfouzi

      I have got a table say something like this:

      ID VARCHAR(30),
      ParentID VARCHAR(30),
      TITLE NVARCHAR(100)

      As it seems, this table has a parent-child relation with itself. the ID column is not auto-generated and is a combination of ASCII characters and numbers with length of 30. Now, I want to move all the data contained in the above table to the following table:

      ID SMALLINT
      ParentID INT
      TITLE NVARCHAR(100)

      In this table, ID is an auto-generated integer Identity column starting from 1. What is the best way for converting the ID columns while moving them to the new table? Thanks in advance for any help :)

      W Offline
      W Offline
      Wendelius
      wrote on last edited by
      #2

      I think you have to do this in two parts. If you have a natural key on the table, it's easiest to use that. Something like (just a draft, may contain several mistakes):

      INSERT INTO TargetTable (Title)
      SELECT Title
      FROM SourceTable

      UPDATE TargetTable
      SET ParentID = (SELECT tt1.Id
      FROM TargetTable tt1, SourceTable st1, SourceTable st2
      WHERE targettable.Title = st1.Title
      AND st2.Id = st1.ParentId
      AND st2.Title = tt1.Title)

      The need to optimize rises from a bad design. My articles[^]

      M 1 Reply Last reply
      0
      • W Wendelius

        I think you have to do this in two parts. If you have a natural key on the table, it's easiest to use that. Something like (just a draft, may contain several mistakes):

        INSERT INTO TargetTable (Title)
        SELECT Title
        FROM SourceTable

        UPDATE TargetTable
        SET ParentID = (SELECT tt1.Id
        FROM TargetTable tt1, SourceTable st1, SourceTable st2
        WHERE targettable.Title = st1.Title
        AND st2.Id = st1.ParentId
        AND st2.Title = tt1.Title)

        The need to optimize rises from a bad design. My articles[^]

        M Offline
        M Offline
        Meysam Mahfouzi
        wrote on last edited by
        #3

        Thank you, seems a very good idea. I will try it :rose:

        W 1 Reply Last reply
        0
        • M Meysam Mahfouzi

          Thank you, seems a very good idea. I will try it :rose:

          W Offline
          W Offline
          Wendelius
          wrote on last edited by
          #4

          You're welcome :)

          The need to optimize rises from a bad design. My articles[^]

          1 Reply Last reply
          0
          • M Meysam Mahfouzi

            I have got a table say something like this:

            ID VARCHAR(30),
            ParentID VARCHAR(30),
            TITLE NVARCHAR(100)

            As it seems, this table has a parent-child relation with itself. the ID column is not auto-generated and is a combination of ASCII characters and numbers with length of 30. Now, I want to move all the data contained in the above table to the following table:

            ID SMALLINT
            ParentID INT
            TITLE NVARCHAR(100)

            In this table, ID is an auto-generated integer Identity column starting from 1. What is the best way for converting the ID columns while moving them to the new table? Thanks in advance for any help :)

            P Offline
            P Offline
            PIEBALDconsult
            wrote on last edited by
            #5

            Shouldn't ID and ParentID have the same type? Are the Titles unique?

            M 1 Reply Last reply
            0
            • P PIEBALDconsult

              Shouldn't ID and ParentID have the same type? Are the Titles unique?

              M Offline
              M Offline
              Meysam Mahfouzi
              wrote on last edited by
              #6

              ID and ParentID are of the same type (both varchar in source table and INT in destination table) Titles may not necessarily be unique. Therefor, I might have a title like 'business' whose parentId is NULL and have another title like 'business' whose parentId is 'POVAD78Y6CSDD8F76NEL876D'. The grand parent of all items is an item whose parentId is NULL (a root element). Could you suggest a good solution please? :)

              P 1 Reply Last reply
              0
              • M Meysam Mahfouzi

                ID and ParentID are of the same type (both varchar in source table and INT in destination table) Titles may not necessarily be unique. Therefor, I might have a title like 'business' whose parentId is NULL and have another title like 'business' whose parentId is 'POVAD78Y6CSDD8F76NEL876D'. The grand parent of all items is an item whose parentId is NULL (a root element). Could you suggest a good solution please? :)

                P Offline
                P Offline
                PIEBALDconsult
                wrote on last edited by
                #7

                Maysam Mahfouzi wrote:

                INT in destination table

                That's not what this says: ID SMALLINT ParentID INT

                Maysam Mahfouzi wrote:

                Titles may not necessarily be unique

                That would seem to eliminate the other suggestion.

                Maysam Mahfouzi wrote:

                Could you suggest a good solution please?

                Maybe... Perhaps you could add the existing varchar columns to the new table temporarily, copy the data over, then set the ParentID values based on the old mappings within the new table, and then remove the varchar IDs.

                M 1 Reply Last reply
                0
                • P PIEBALDconsult

                  Maysam Mahfouzi wrote:

                  INT in destination table

                  That's not what this says: ID SMALLINT ParentID INT

                  Maysam Mahfouzi wrote:

                  Titles may not necessarily be unique

                  That would seem to eliminate the other suggestion.

                  Maysam Mahfouzi wrote:

                  Could you suggest a good solution please?

                  Maybe... Perhaps you could add the existing varchar columns to the new table temporarily, copy the data over, then set the ParentID values based on the old mappings within the new table, and then remove the varchar IDs.

                  M Offline
                  M Offline
                  Meysam Mahfouzi
                  wrote on last edited by
                  #8

                  PIEBALDconsult wrote:

                  That's not what this says: ID SMALLINT ParentID INT

                  I'm sorry think of those both as SMALLINT.

                  PIEBALDconsult wrote:

                  That would seem to eliminate the other suggestion.

                  You are right, at that time I didn't know titles are not unique. I just found some duplicate titles. I can't rely on old VARCHAR values to calculate new ID and ParentIDs in SMALLINT. How is it possible to move a string like 'LSFDKJSDLFKSJDLJSLDK' to a SMALLINT column?

                  P 1 Reply Last reply
                  0
                  • M Meysam Mahfouzi

                    PIEBALDconsult wrote:

                    That's not what this says: ID SMALLINT ParentID INT

                    I'm sorry think of those both as SMALLINT.

                    PIEBALDconsult wrote:

                    That would seem to eliminate the other suggestion.

                    You are right, at that time I didn't know titles are not unique. I just found some duplicate titles. I can't rely on old VARCHAR values to calculate new ID and ParentIDs in SMALLINT. How is it possible to move a string like 'LSFDKJSDLFKSJDLJSLDK' to a SMALLINT column?

                    P Offline
                    P Offline
                    PIEBALDconsult
                    wrote on last edited by
                    #9

                    Maysam Mahfouzi wrote:

                    I can't rely on old VARCHAR values to calculate new ID

                    No, you're using auto-increment for the new IDs, right? ID,ParentId,OldID,OldParentId,Description (or whatever it is) INSERT INTO NewTable (OldID,OldParentId,Description) SELECT * FROM OldTable (add an ORDER BY clause if desired) And you get all new IDs. You also have the original mappings. For any row with non-null OldParentId, match it to the row with that OldID to get the (new) ID to put in the (new) ParentID. Something like this:

                    UPDATE NewTable
                    SET ParentId=B.ID
                    FROM NewTable A
                    INNER JOIN NewTable B
                    ON A.OldParentID=B.OldID

                    (I think I'll go test that now.) Then remove the OldID and OldParentID columns (if desired).

                    M 1 Reply Last reply
                    0
                    • P PIEBALDconsult

                      Maysam Mahfouzi wrote:

                      I can't rely on old VARCHAR values to calculate new ID

                      No, you're using auto-increment for the new IDs, right? ID,ParentId,OldID,OldParentId,Description (or whatever it is) INSERT INTO NewTable (OldID,OldParentId,Description) SELECT * FROM OldTable (add an ORDER BY clause if desired) And you get all new IDs. You also have the original mappings. For any row with non-null OldParentId, match it to the row with that OldID to get the (new) ID to put in the (new) ParentID. Something like this:

                      UPDATE NewTable
                      SET ParentId=B.ID
                      FROM NewTable A
                      INNER JOIN NewTable B
                      ON A.OldParentID=B.OldID

                      (I think I'll go test that now.) Then remove the OldID and OldParentID columns (if desired).

                      M Offline
                      M Offline
                      Meysam Mahfouzi
                      wrote on last edited by
                      #10

                      So I think I've got to add OldID and OldParentID columns to the destination table, move data the way you've suggested and then remove OldID and OldParentID columns. This solution seems quite perfect and beautiful and I'm going to test it as soon as I have access to database. I'm also thinking of a way to move data without using those two temporary columns in target table. I really really appreciate your help and time PIEBALDconsult :) :rose:

                      _

                      P 1 Reply Last reply
                      0
                      • M Meysam Mahfouzi

                        So I think I've got to add OldID and OldParentID columns to the destination table, move data the way you've suggested and then remove OldID and OldParentID columns. This solution seems quite perfect and beautiful and I'm going to test it as soon as I have access to database. I'm also thinking of a way to move data without using those two temporary columns in target table. I really really appreciate your help and time PIEBALDconsult :) :rose:

                        _

                        P Offline
                        P Offline
                        PIEBALDconsult
                        wrote on last edited by
                        #11

                        Just got around to testing it:

                        INSERT INTO NewTable (OldID,OldParentID,Description) SELECT * FROM OldTable
                        SELECT * FROM NewTable

                        UPDATE A
                        SET ParentID=B.ID
                        FROM NewTable A
                        INNER JOIN NewTable B
                        ON A.OldParentID=B.OldID

                        SELECT * FROM NewTable

                        It worked on the small amount of data I created to test it:

                        1 NULL 1 NULL Section 1
                        2 NULL 2 NULL Section 2
                        3 1 1.a 1 Section 1.a
                        4 1 1.b 1 Section 1.b
                        5 2 2.a 2 Section 2.a
                        6 2 2.b 2 Section 2.b
                        7 3 1.a.i 1.a Section 1.a.i
                        8 3 1.a.ii 1.a Section 1.a.ii

                        M 1 Reply Last reply
                        0
                        • P PIEBALDconsult

                          Just got around to testing it:

                          INSERT INTO NewTable (OldID,OldParentID,Description) SELECT * FROM OldTable
                          SELECT * FROM NewTable

                          UPDATE A
                          SET ParentID=B.ID
                          FROM NewTable A
                          INNER JOIN NewTable B
                          ON A.OldParentID=B.OldID

                          SELECT * FROM NewTable

                          It worked on the small amount of data I created to test it:

                          1 NULL 1 NULL Section 1
                          2 NULL 2 NULL Section 2
                          3 1 1.a 1 Section 1.a
                          4 1 1.b 1 Section 1.b
                          5 2 2.a 2 Section 2.a
                          6 2 2.b 2 Section 2.b
                          7 3 1.a.i 1.a Section 1.a.i
                          8 3 1.a.ii 1.a Section 1.a.ii

                          M Offline
                          M Offline
                          Meysam Mahfouzi
                          wrote on last edited by
                          #12

                          Thank you again, I tested it and it worked for me too :) :rose:

                          1 Reply Last reply
                          0
                          Reply
                          • Reply as topic
                          Log in to reply
                          • Oldest to Newest
                          • Newest to Oldest
                          • Most Votes


                          • Login

                          • Don't have an account? Register

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • World
                          • Users
                          • Groups