CODEWITHSUNDEEP
sqlsql-serverinsert
Dima R.
Dima R.

Reputation: 997

Insert values into table from the same table

Using SQL server (2012) I have a table - TABLE_A with columns 

(id, name, category, type, reference)

id - is a primary key, and is controlled by a separte table (table_ID) that holds the the primary next available id. Usually insertions are made from the application side (java) that takes care of updating this id to the next one after every insert. (through EJBs or manually, etc..) However, I would like to to write stored procedure (called from java application) that 

- finds records in this table where (for example) reference = 'AAA' (passed as 
  parameter)
- Once multiple records found (all with same reference 'AAA', I want it to INSERT new 
  records with new ID's and reference = 'BBB', and other columns (name, category, type) 
  being same as in the found list.

I am thinking of a query similar to this

INSERT INTO table_A
       (ID
       ,NAME
       ,CATEGORY
       ,TYPE, 
       ,Reference)
 VALUES
       (
         **//current_nextID,**
         (select NAME
          from TABLE_A
          where REFENCE in (/*query returning value 'AAA' */),
          (select CATEGORY
          from TABLE_A
          where REFENCE in (/*query returning value 'AAA' */),
          (select TYPE
          from TABLE_A
          where REFENCE in (/*query returning value 'AAA' */),
          'BBB - NEW REFERENCE VALUE BE USED'
          )

Since, I don't know how many records I will be inserting , that is how many items in the result set of a criteria query 

 select /*field */
          from TABLE_A
          where REFENCE in (/*query returning value 'AAA' */),

I don't know how to come up with the value of ID, on every record. Can anyone suggest anything, please ?

Upvotes: 1

Views: 8897

Answers (1)

peterm
peterm

Reputation: 92845

It's not clear from your question how sequencing is handled but you can do something like this

CREATE PROCEDURE copybyref(@ref VARCHAR(32)) AS
BEGIN
--  BEGIN TRANSACTION
    INSERT INTO tablea (id, name, category, type, reference)
    SELECT value + rnum, name, category, type, 'BBB'
      FROM 
    (
      SELECT t.*, ROW_NUMBER() OVER (ORDER BY id) rnum
        FROM tablea t
       WHERE reference = 'AAA'
    ) a CROSS JOIN 
    (
      SELECT value 
        FROM sequence
       WHERE table_id = 'tablea'
    ) s
    UPDATE sequence
     SET value = value + @@ROWCOUNT + 1
     WHERE table_id = 'tablea'
--  COMMIT TRANSACTION
END

Sample usage:

EXEC copybyref 'AAA';

Here is SQLFiddle demo

Upvotes: 1

Related Questions