More efficient INSERT "blank row" statement.

• m.berggren

  Ten Centuries

  Points: 1203

  More actions

  December 2, 2009 at 1:33 am

  #96126

  Hi,

  Here is my table.

  table t1

  --

  seq int PK

  num int unique

  name char

  On num 100, 200, 300, 400, 500 .. etc I have a header. Starting with every hundred.

  I want to insert a new blank row before every header in my table with the num 99, 199, 299, 399, 499 ..

  For now I do this manually:

  INSERT INTO t1 (num) VALUES (99);

  INSERT INTO t1 (num) VALUES (199);

  INSERT INTO t1 (num) VALUES (299);

  INSERT INTO t1 (num) VALUES (399);

  INSERT INTO t1 (num) VALUES (499);

  Must be some more efficient way to do this.

  Something like ?

  Insert a new row for every row in table and set the num value minus (-) 1

  //

  Regards

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 2, 2009 at 5:06 am

  #1086372

  if your table t1 already has those values, you should be able to use the integer modulus to determine/select only those values that are perfectly divisible by 100; then do exactly as you said, insert that number minus one.

  INSERT INTO t1

  SELECT num -1

  from T1 WHERE (num %100) = 0

  note

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• m.berggren

  Ten Centuries

  Points: 1203

  More actions

  December 3, 2009 at 1:01 am

  #1086935

  Thx Lowell!

  It worked! I first had some issue regarding the modulus operator "%". Beacuse I use ASA (sybase) in this application and in ASA, the percent sign is a comment marker. So I first had to turn this option off then it worked.

  Working code below:

  SET OPTION PERCENT_AS_COMMENT=OFF

  INSERT INTO t1 (num)

  SELECT num -1

  from myDatabase.t1

  WHERE (num %100) = 0

  //

  Regards