How to insert multiple records from fields in select record

  • DBA.k

    Ten Centuries

    Points: 1085

    tshad - Tuesday, January 29, 2019 7:59 PM

    That's fine.
    But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:


    DECLARE @TestTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     Name1 VARCHAR(20),
     Name2 VARCHAR(20),
     Name3 VARCHAR(20)
    )

    DECLARE @TestTable2 TABLE
    (
     ID int IDENTITY(1,1),
     NameRecord INT,
     SingleName varchar(20)
    )

    INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
    INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
    INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

    SELECT * FROM @TestTable

    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ???

    The @TestTable looks like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQMAAABQCAYAAAAdvBAmAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAx7SURBVHhe7Z0/iOvYFcY/J02qfQvbLNskS+RXGHVJZVfbxZ4UZgt1wZDCrhabwEAChhBwN42nCdjFggmkUBIwZG2lypLC7rYThowUNs0+0gTe2ypNMjnn6srWeDy2x9Zfz/mBGPlasnSupHPPufb5pvSHP/35HoIgvHhK33zzzf1HH32kXybPmzdvkObxLolL6TuxI3+wLd/R64IgvHDEGQiCoBBnIAiC4mln4N+iVruFH66XSiitlxo6jnonPtQxariNfmz0HOLmkU2hXfr9pInTtrT7LoSO0Sl1EFuXZWCH06mtr3/twYHPIFM7Tr+Hj48MqkN49/e458XrozIox9d5a5botRK+gaNEbaLFm5twBykeP1bS7DsftzW68co9jHVLfKRoh9NBA5a+BzxYdjnGwSBlO1xtBz2baJzmoE9LE4w6upMhYM/iNZYezqFp4yat0XkLo96EuVzB068LRap9Z6C74AdojrZuiY007aiPcD/qkjWMgSurCvcupjs6bTsW2g6jjErVxSlmnD5nYLxO5MG5urZ2j87k/TZhvQ6FVOjVQUeHSLVbB7dqPZLG0DYdHsV4P9p2XzDjO1O41QrK6sUT+/nOpn3PcdQ98OD8qC2yTa1l09gRL1n2XZxkY4ePmb2E+TpwDXGQiR3+DPbSxClmnDGBeLoH2ovRRX+XRyXvtwhDei/SyUsXlesFtVGk0hsAap3f56iFwll66CoTvd+kAjsaui17KK8vSgll2r0/YQ/71H7c3gAsT58Hh2Qtuijc3qOdg3bPcilS0wbw+TUn1H6Nu8g2k74ZvB8nafZdkmRgh3/bQg9DXNd1QxykbId/S86DUjcMr3GKGWc4Aw+rEz3QIeojesi2ParyioHXZIPXo2rVwlV4DtF1RnlJyt3K+oHn/ZY2ZuEHR+cMvDndCi6mHOo8tZ9D7XzDdPVBKF0a3S/QhW6vB+3GlYWqexecP58Tt/NnRrcpV1BVa/GSWt8lTJp28EPUsinnDkPtGEnTDqNLzuOMuY/TnYF/twmpY6eOa8tG62alX1NoXiav2OcRlh/c4TMepDbm4QOvFnp4d11xngeh0Xo8DXtxx37JGBszGfRdIqRjBzuCMjmCSQKOICDt63H63MdpzoDyZg6LzX5SHcherg9zPI7k1RRWl4Oj+bNVpH0PxhWs6hjTMLkij7z36516E+3xFM5T+3E7BZM363a6sJTT3UK369zOn9lYmq8f9k24b3QbtRY/mfRdAiRuB623eibmiTmCgKTtUOkBhQLBO6fPfRzvDKL5NSXXK8p9R3HmV48gjzoMfSavA3Y5OH7LdnX7IQx0J3PeMTjv1gqWmhN4inAe5Kn9ttp5kmE+Ie/M7XSCg6CdRxrvUecE27iNYBv6yETShIAs+i4JkrXDueEQfYwGt+sl/q/LmWTtMLoTzDHQz2cLK+u0Z1MKlQqEFPjkCylUEgThIhFnIAiCQpyBIAgKcQaCIChKb9++vX///fdBf5HWX0EQ8sWrV68CZ/Cf/6YXIHz4wXvqBxPC83n37p26aEXnsux4p18Vl3fvXqnrobwAP6BpIZGBIOQT5Qz+9e9v1Ys04FRBEIT8IZGBIAiK4yODr3+L6w+6+Kt+eSqHIwP+vf/m56Fcz53IL0QvEKcT7bfIckoJW5ZkUAeRCP7nqJU+fXj/clvtc23bl3Sv/4ru+CiRtgfbRniq/UyOiAz+gfFP3sOHP/4lfqdbzuG4yCBanZVmpVyxqY/CPmMFokgfJltEIuzlK/Ra8T+4SXBEZPBDtP/yLW3zR/xMt5zDwcgg0dJoQUiZ6q8xNL94LHCSQ/I5Z7C00QrlwSRHiAcKvffLsj0htZUnjpALY9vW65H0iFOorLKlq+ufwh3kPzo4fs4gJo76NqFqoc/yTt4cph2nYu1L5YAs25NSWzljn1wYS8uxUEh0nbUp1I4OpuM2mlllS8bP0S9AdJC/yMDoYrHoQqmDGXVcx6lY+1LZllzblmUL52S2pbbyhopu9Ki/LRembVuvs/5gewwlXOVMMW43keXMSX30GZDz6CCfkYEgPOL5cmH1ZlvJ2DlTF8NYlU5P4RMa2L5A6yaqJ/4xKtW/PxQV9r+GWy1nMmeWu8jggYST7+CGolsr18NVAdglubYty1YInikXxjJ2bkP9g5E83EJG9zOY499HzvsHeG1+hZX3T/2a7PI8ujYfZ3JtchcZPJBwYlmxIcuK6TeFEzlGli1nbMnYlzo4QS6sjib5j6p1lRPHR9HB8Ed6PaA++hsq01+gVvtULa1pma7NJ/pdYvkb6ofvk8166Xy5v/0M1lWLaTmE7333fxdRpJIFUqj0XCi1qE3RXIwSmS+4yEIlmTMQLg6nQyNmA6512j8UeYnk79sEQYiD+khNNC4kxzwaiQwEQVBIZCAIgkJNIPIKj9j8oKb1VxCE/MATiMoZpDlDfSkz4llwWd8mXMos/IV9myAIgiDOQBAEhTgDQRAURzkDJ6wPpyVpfQFVmxD+BHW96Pp7YT9c2/+o73Tdf5EomFzYkxTMjsPOwOmgAQueqiP3YCWsL2B0F0FVWrhwdVrG5aeFojrU1ypYvLm5qfsvFMWRC9tPwWTP9sK/5BqF/5ffwFXK+gLOjQ0r8/LT4mLUmzCXK0QLZwtBgeTC9lI02bPj8TGzlzBfp/QTTwp7BzkpPy0qvjPdaEoqcRCdPmxJnwVyZ/lKK4oiF3aIQsmeHYt/20JPKebohoThunuzH0YlwlFslf5yFXh/wn24X/psxVJhnFrkKa0oiFzYQYoke3YMPLHX4jr4RVoPp4Mb28xOt66oROcMvDm5bhdTzhEOSJ+F7ShXgr85oQhyYcdQGNmzQ7AjYEGMSWqOgI85kPLTczHq6PZNJf1VXPIvF3YcBZE92wvllK2eiXmKjoAOCsoQRO4sDlj6i1WCCyx9lne5sGMphOzZPpwbVqEdoxHJQxP/XwY+eU+rL3JnsVCm0cel0aeA0mdr8i0XdjwFkD2TQqViIIVK+UIKlQRBuEjEGQiCoBBnIAiCQpyBIAiKteyZIAgvF55AlG8TCsRlfZsgduSF0A5JEwRBUIgzEARBIc5AEATFUc5gI3uWTq172se7KKLSZ7Uabrn/lGZBjGXJcX/eNqENDy6+j1vWYkjyuHEThx1J93WEw86AZc9cLXvm9YGGFsVIirSPd1E46JRtWJ4uYZ5YsIvaf9XqpsSa4RLsTYVPcSiQHYedAcuehRWLRlj0ot5JBP/O3fw/faOO66GLQlfgpol/B7caUYYyuljcJ/PvyJPHgmXamIX3mrcC2m1U9cviUBw7njdnoLyaiSRVz4zXJpb2THtSH7PVMlXNxUITlilTbuA/6rIVZrvSLwpDo1JoqiBVhaYddMLtI2Gu0ymhM9MvEuaqacLWT5EzdWE1I8IrFEFulKC1PYfOO6NB5Sw7ylw1HEKRH6V+HV2GHjdHOwMlYU4nhmHCgiMUiczJkyrprtoN3cLFGwuyg8uU56isBiiXg5trXW2+U9qMpdBsVCZhWlGB3dL5KW1f4e1ZnZr1ENSHOJiO22heqRfJw6pLamCg47IWZlTxg+6TBZ+zSietjVTbvvPOKkQ61w5+7bMjGNC1WmAUqlLFzNHOIJAwT14qnamPtFz6YoRmxC8KR8DqRrr/WCa9Fz7cu6TNVKS3RE85Dlp4FFpSSMtiPJxu8PaUavTb4yBVc6YYpylbryKdFTwWdd0WYlERjY4AoqNnHs57m3PsYOiatFoNjM1kNT6elyaQGelKpfu4c5GeGvOFwTLp7YMy6W3Mw5FJLQt0tzS36s22kk7jEHeYqmw9328uBgMX5oNhPZgorfR1pBOOnltkd97bnGcHPQHoTzwM3UaiA/FBZ6DSAzqD4PFPQSqdcqhSJxjNfOcGvWWG4V3BCK6VjgT4NY+IoUz6LnjEqtLoGeYSKlfd7L+GnQrdiPwtT9pKdCzciuUuYVyTIpzgZPzZanf8mOF5b3OWHXwNjUCpyk3w26GDzsDoTjAH5aBqgqOFleVRzqLfTAKeM0AwZ1BuuGjPizobnj7BtdLzLdx/A2CuZNKfIphjAKV+KkxtrWDt3L6OJt2z62950mTnNyL8LROfdmBny6bwcScZnvc2Z9mhoc+YDF00EvrdgRQqFYjs+o4nr6ZoLuJxzOnZEe95byOFSsLLgtO2UqN4svVFPe8MkcigQFzaSFR0JDIQBOEiEWcgCIJCZM8E4YUTpjqle/61gyAILx5JEwRBUIgzEASBAP4PualOv0fw53UAAAAASUVORK5CYII=

    The results should look something like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMEAAACPCAYAAABDEL+AAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAuySURBVHhe7Z2xcuI8FIUv/7NAiixPYJ4A0qSiTWfK0KSjpEtDSpjZIm2qzOwsPEF4AjZFoN7HWH5Jlm3JGAMmccy955vRJNiY2EJH0nV0fBt///7dkmI+n1Ov19O/gm8G30U1xPXc+Pnz5/bfv3+kCwASSUaCX79+URiGZiP4Xv78+UM/fvywr8BXEdfzf/Y1AGKBCIB4IAIgHogAiAciqILFgBqdJ9rYl3VlMWhQY7Cwr+RQSgSmshoN6jz5X+vmqfNNlbihp050TmkZEJuvU4vIuTa3is138U0Cq187KEfpkSAIAloOH2vV0ILJmrbbrSnzcEY9Dr3a5ok6vRmF8+i6tts50Tht9N2p2vZ2T037umrq2A5Opfx0qD+iSXCgoekvMLcH0z13h542Cxok+3XP7fboer99uyHb2xf39N3bkGj14fSQB44vPNd9x+nzV+f5FPfUdp/3WWr/h3nzGQR03bK/UpemTqP3pzDRuQ4Wbr1m6jEzqpiy9zs8os5r3g6O4YyYoEX3zxMKZuPMScaoC7sjerY983oS0MzpwYiWNGy90q3Zv44qstGi91H8fqLhXfr+xaBFw/bc9oZ6/4p6e6cBqqLGM6L2ldNYio5X59oaEiUjSdzb6grPOc6reHUd6tC52T9VTVQdc+d+1ojeh+pcytK8oX6g6yrbGPYz643peh2d7zxUxz7GZ6uus7dSpxZfS0BqiKHttGv3+xxX53VuB0ei/2Osy3Q6VZ95HPOQtmrqkfyu5iFb/Upd4FaP27msJ9uAwm20d71VdbF137pz7Dzcqm8oer85NtjaP2mZb8NkW/R56nKSEp+f4dDx7t9y8c45xv27+nf/OnKP0Z9v6+gYVquV/S3F1LO5Nv86zPbkBHbr1fvb2XPLnJf3WQfrPHp/vdrBacT1fPbdoe50TuFySHc53YAJkOJhS/W0S7u9HLrHcIfBHmX71yQmUN/I7jz1wPHBterTMqzfc865RdeqZ35f25dZco85HzP3j3vGE0YFj+YVtWlFH/bYxas/Wu5yuM5j6tQOTuVsEZg5qm10v+0Wg5p7toZtO01QRX17avA9gzD9rKS80X3eN9h9MMPq2PtCDhy/fKeddt26zjnnNb0v3Tl6htxjPo/m/bO6tgIRFrH5UBJIG1Fvpupkz1Qo4oQ6r2M7OJJPEIHCNrrhcL/GF49n9ABmXpxt1EU06X7kjAaHju/eqqp196t5rJ5n2uPcoG/zNKZZ0KebfZVuelvns+zdnbLoXtS7Bbl4pGGRCIvQo5SOAZLGo2OYPZxc54ratYPj+BwR6EangyP7ytCdRrcp7bA1vp6ohlYW9flva+q/tJxhUJWigMg27NdIBQeOV71YNM+w+1Rg+azvwOjj1DA/6yXHtF76tC68JZn5rNY7jc7o/XTP7523CWxL9nz6O6H0WkzZW4cl6lwfU7d2cAxlAmPwteQFxp+BCTozAbob3Erj0wJjcDms39VExAuEN/Sxsr8KBiIQRHe6psnKnQ616KW/prdzokoGQASi0HNq967KVrwANJ7HuNPp2M0AyAEe4xoCj3E1wGMMgAUiAOKBCIB4IAIgHogAiIeJCLJuI1WKnE4Xw8JxXGXL+Y4qEMFqJAgTH64qhUuEL4UuTePrMYvwgsQVVrgCFJwEpkMXT5HnVu871cMrD1YimPWkfbG6MR/hgT7BwysRJiLw18REy/kFzJk3v+ll6bvDmvcjCh0LpSacx1OnJt30A72B4kOaV+18V50gWE6HooYQG2oYU8YDDXbgGRMYL21JC+IlUcYDDXZgIYLFwI8BjI+1yAfMhTIeaLADCxF0b9vJExR06a0mB3zAXNCx0KkeaJAFS6lrCJZSVwOWUgNggQiAeCACIB54jIF4EBjXEATG1YDAGAALRADEAxEA8UAEQDwQARAPExFw9Rhromtjczk1hNVIwM9jDKoA06FLZm9+YO0p7mTyK+9uG3g5kDXRqJPNUM8dViKQ5TFWjfpQfmAvv/LutqlOeD57TW2oxq4ZUF+YGYGJCCR6jLteZvvmTZ+CjFc49RaneNu8vG5aAy+0DEdnZYK8RFhOh6R4jM/PD9ylBz2CmIra0O+XJYW38mIpnjGBBI/xJ+UHNiOInhLZJ1cI1AAPEYj1GDuUzg8c+5T1SBLe7kyfJMBCBBI8xmnQr4sSfeuz8gPbZxEpJE6FNFhKXUMqX0qtplaNnr5rJOv5plhKDRIWrzM9DIicCmkgAvEsKNKAVAlABED1//rx75JXmcBjDMSDwLiGwGNcDQiMAbBABEA8EAEQD0QAxAMRAPHwEIH+t3+yrsYtHDwFOf5pFtdVH3iIoDtNDDVxmeu7vYyWAgSTtXNtfnYacB48p0ObJxrPApo88Pw3aFfbIlcfjpUyO1q4I0X8tAo3l7EE++nxsBSBWVvP1iaoGvV4RtS+skvFdSM/lMtYL8Ue07XNhj8PlzR8xEgSw1AEPBeELYct24u36KWvpkbxYp+jcxm/JZ3C7kgiG3YiiLI3TojbTCiJCVSwsxw+pr08chmfDTMRLOhxuKRwxDh7Y/eBJsGMxvGkHrmMz4aVCMwooCYCvJfGN+l+5IwGyGV8NoxEEI0CweSBv0PKe14QchmfC5ZS1xAspa4GLKUGwAIRAPFABEA88BgD8SAwriEIjKsBgTEAFogAiAciAOKBCIB4IIKLJjbM2JcVsNhJ9nf58BGB5zNm5pzKeKjhrPxceIjAPF9/RRPrnNrOddIOJmZ0naa1N3NyNM+JvCyV4FxYiGDzsSJylw6bVZa+s+qycb0BftbKiCL/cJH/GLmNNSxE0LxqE2XSl+qcvSycVcYvsFQj2/4pXpF/eDHI8R939uc7lpjbmMd0qOvn72o0xrQqk8qxlkQ5mk3jNnnZdsWw1z+c89QN4z9evtBv5zOk5zZmExh3p/GcWZcRtUulcqwv8fVFicpPCfxj8cSlp5p4EfJyG7MRgcfila3Nsnn/TJOTTPRhmus4KenIkYe03MYMRaCCvd6Mjc1SZ633gtLFIw2PNdFb/3Fiyj8WYbmNeYhA30Z0hvvVZE1vTCaxuufvv8TPHFLF3Aou7slTdDyx9o/XxQuM85CV2xhLqWtILZZSC8htjKXUoBBJuY0hApCDrNzGEAHIQVZuY3iMgXgQGNcQeIyrAYExABaIAIgHIgDigQiAeCACIB4+ImDrMU6dYbne4vi6D64HAvvgIQLOHmNLEAQ0y/EW6+UNuLV9HixEcCke43Sk8ssxtEe7jrDIORbS7a19HeOtqnVHEHiK82AhgkvyGPvmFvN/yiOF0KXbcEkvjgoi62N2kZtq6HdEz/bz19ol5o0g8BRn4TEdYu0xTtH+4TR9q87RRjlZ+/2nURiXWKaDkO4pzsImMObuMTa4DVZbSPdkqNRutHgq1NDuMLs9H3me4ixsROBRE49x0hBtKeK496UNVgfEQf8m8/whxWJArWE79RWvJzl5jn2keYqzMBRBvTzG6egUlSx5+9ttFePsIWqwPeqpgHh0xJxloeZMBwdFYZ7iLDxEwNhjvINpsOrnvsaaiY/G1xM1Jh5Clqc4C5ZSfyG6Eeb1/kXE06FTjzsbAZ7iLFhKDTwkeYqzQARfTDRFS0uWQ/urQZanOAtE8IVUPqUpjSxPcRZ4jCtE3/XJCiPu/VerlfkJqgeBcYXoBr9PBO52eIyrAYHxNxHP/eMCvh+IoEIuJ0aQBURQMVoIeQV8HxABEA9EIJ7qcyHXDT4igMfYMc6AU+AhAniM7StQBhYi4J/H+Os8xjsdhRlZOI2kh2EhAtZ5jBO+xmPsHZuMqMemg+IBj+kQPMYOJ3qMYz7U6CFQABo2gTE8ximneYwjZmp44Fhlx8BGBB5s8xh/jcdYE85tovA7eXeZGIqgXh7jz+ZLPMYWkyichtQS9k8DHiKAxzillMc4Ruc9nlOoRCbpCXRYSl1DsJS6GrCUGgALRADEAxEA8cBjDMSDwLiGIDCuhqief9D/9cC0luhFHkYAAAAASUVORK5CYII=

    I could do this with a cursor but would prefer not to.

    Thanks,

    Tom

    Use Unpivot you will get the expected result

Viewing post 16 (of 16 total)

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