Need a Clean Way for this Insert

[dougcoulter]

Hello all - I have stumped myself on the following. I am writing a stored procedure that, among other things, will insert values from one table into another. The source table has 59 fields - 57 of which I need to use to insert into the destination table, which in addition to these 57 fields, has a 2 other fields that I need to provide programmatically. Does this make sense?

The brute force way is to declare a boat load of variables and return in them the 57 fields from the source table - using them in turn for the insert, but there has to be a better way.

Any suggesstions?

Thanks !

 

[SQLSister]

If you are inserting from another table, why on earth would you consider using variables to do that?
USe a select statement instead of the values clause. SOmething like:

insert into table1 (field1, field2, field3)
Slect field1, field2, 'test' from table2

To get the programmic parts you may just need a formula where I put 'test' in the select or maybe use a case statement.


Questions about posting. See faq183-874

 

[557]

just give an sql statement to do this insert

insert into desttable
select * from sourcetable where fieldwhatever < 100

(or whichever condition you want) it's very easy

 

[dougcoulter]

Thanks SQLSister and 557 - I was just not seeing the forest through the trees. It was a long day...!