Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have this formula in an excel column and want to duplicate it in all the rows after the current row.

="INSERT INTO MyTable (ISAM_ID, ENTITY, CUSTOMER_MEDIA,STATUS,IIN,OID,FVC) VALUES (" & A2 & ", " & B2 & ", " & C2 & ", " & D2 & "," & E2 & ", " & F2 & ", " & G2 & ", " & H2 & ")"

How is this done in Excel?

share|improve this question
could you please elaborate ? you can copy via drag and drop (left-click on the square on the bottom-right and drag the cell to the others you want to copy to) – JMax Jun 27 '11 at 9:02
@Farou Alhassan: It is considered polite to accept answers to your questions. To a large extent, you have neglected to do so. You may want to go back and accept answers to your previous questions. This may motivate further help from other StackOverflow users. – Jean-François Corbett Jun 27 '11 at 9:10
Oh, once again, the great xls2sql to the rescue! – Rodrigo Jun 27 '11 at 10:32

migrated from stackoverflow.com Jun 27 '11 at 11:14

2 Answers

up vote 0 down vote

As @Jamx states, Copying the field down into subsequent fields works.

Doing this kind of sql construction you have to be careful that you don't break the sql statements you are trying to construct or include characters that are special to the sql environment you use to load the data (sqlplus for oracle has a define character usually &)

Note: You have a problem in the sql you are constructing, in that you need to enclose the strings you are trying to construct and probably add a sql termination character like ;.

i.e. The formula should be:

="INSERT INTO MyTable (ISAM_ID, ENTITY, CUSTOMER_MEDIA,STATUS,IIN,OID,FVC)"
  & " VALUES (" & A2 & ", '" & B2 & "', '" & C2 
  & "', '" & D2 & "','" & E2 & "', '" & F2 & "', '" & G2 & "', '" & H2 & "');"

The sql output should be:

INSERT INTO MyTable (ISAM_ID, ENTITY, CUSTOMER_MEDIA,STATUS,IIN,OID,FVC)
   VALUES ( 10001, 'ErferrRR01','MP3','LOADED'
   ,'RRGEE', '232133122dfcsdds212121','TGEE');
share|improve this answer
Additionally translating the special characters to encoded characters such as translate(string,"'","''") – Andrew Russell Jun 27 '11 at 9:41
up vote 0 down vote

Click on the little square on the bottom right (called the fill handler) and drag/drop to the desired columns:

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.