Advertisement
pcwizz

random sql query

Feb 19th, 2014
121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.27 KB | None | 0 0
  1. #create temp table
  2. CREATE TEMPORARY TABLE temp_user_data
  3. (
  4. USER_id INT,
  5. name TEXT,
  6. contactNumber TEXT,
  7. email TEXT,
  8. password TEXT,
  9. salt TEXT,
  10. gender TEXT,
  11. ADDRESS_id INT,
  12. lineOne TEXT,
  13. postcode TEXT,
  14. GROUP_id INT
  15. );
  16. #populate temporary table
  17. INSERT INTO
  18. temp_user_data
  19. (
  20.  `USER_id`,
  21.  `name`,
  22.  `contactNumber`,
  23.  `email`,
  24.  `password`,
  25.  `salt`,
  26.  `gender`,
  27.  `ADDRESS_id`,
  28.  `lineOne`,
  29.  `postcode`,
  30.  `GROUP_id`
  31. )
  32. VALUES
  33. (
  34. ?,#User_id
  35. ?,#name
  36. ?,#contactNumer
  37. ?,#email
  38. ?,#password
  39. ?,#salt
  40. ?,#gender
  41. ?,#ADDRESS_id
  42. ?,#lineOne
  43. ?,#postcode
  44. ? #GROUP_id
  45. );
  46. #Insert User row
  47. INSERT INTO
  48.  `USER`
  49. (
  50.  `id`,
  51.  `name`,
  52.  `contactNumber`,
  53.  `email`,
  54.  `password`,
  55.  `salt`,
  56.  `gender`
  57. )
  58. SELECT
  59.  `USER_id`,
  60.  `name`,
  61.  `contactNumber`,
  62.  `email`,
  63.  `password`,
  64.  `salt`,
  65.  `gender`
  66. FROM
  67. temp_user_data;
  68. #insert the users address
  69. INSERT INTO
  70. `ADDRESS`
  71. (
  72. `id`,
  73. `lineOne`,
  74. `postcode`
  75. )
  76. SELECT
  77. `ADDRESS_id`,
  78. `lineOne`,
  79. `postcode`
  80. FROM
  81. temp_user_data;
  82. #Insert row in linking table to link the address and the user
  83. INSERT INTO
  84. USER_ADDRESS
  85. (
  86. USER_id,
  87. ADDRESS_id
  88. )
  89. SELECT
  90. `USER_id`,
  91. `ADDRESS_id`
  92. FROM
  93. temp_user_data;
  94. #link user to a group
  95. INSERT INTO
  96. USER_GROUP
  97. (
  98. USER_id,
  99. GROUP_id
  100. )
  101. SELECT
  102. `USER_id`,
  103. `GROUP_id`
  104. FROM
  105. temp_user_data
  106. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement