61673

Store procedure to copy a table to two anoter table

Question:

I want to copy a table in to two different tables for this I have written following procedure. I want to concate temp1 table's fname and lname and want to put in another table atom with a string value 'Person'. After inserting the value at table atom I want inserted id and with this inserted id(g_id) insert data to new table user.

DELIMITER // CREATE PROCEDURE cpy() BEGIN insert into atom values(name,type) select concat(temp1.fname,temp.lname), 'Person' from temp1; SET g_id= LAST_INSERT_ID(); insert into user(id,mobno,password,fname,lname,mailid,dob,gender) select g_id,mobno,password,fname,lname,mailid,dob,gender from temp1; END // DELIMITER ;

This code is not working shoiwng error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selec t temp1.fname, 'Person' from temp1; SET g_id= LAST_INSERT_ID(); insert int' at line 3

Answer1:

Try:

<pre class="lang-sql prettyprint-override">DELIMITER // CREATE PROCEDURE cpy() BEGIN DECLARE g_id INT; insert into atom /*values*/ (name,type) select concat(temp1.fname,temp.lname), 'Person' from temp1; SET g_id = LAST_INSERT_ID(); insert into user (id,mobno,password,fname,lname,mailid,dob,gender) select g_id,mobno,password,fname,lname,mailid,dob,gender from temp1; END// DELIMITER ;

Recommend

  • Stop duplication of data in left join
  • PHP and PostgreSQL Transactions?
  • Embed multiple jpeg images into EXCEL programmatically?
  • Does sql server minds the way records where inserted?
  • Index.php as custom error page
  • Linux command line : edit hacked index files
  • Insert records if not exist SQL Server 2005
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • PHP - How to update data to MySQL when click a radio button
  • JavaScriptCore crash on iOS9
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Importing jscolor library in angular 2
  • QuartzCore.framework for Mono Develop
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Arrays break string types in Julia
  • How to format a variable of double type
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • embed rChart in Markdown
  • How to stop GridView from loading again when I press back button?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF