need insert statement

i'm not sure this can be done with the current table structures
(you maybe missing a sequence column on tbltemp)

please provide some exmple data
and restate your problem in business terms/context

do you mean in tblTemp you have tblStatic_Field_Name filled with tblStatic column names?
i think this is how your tables and data looks

tblStatic has columns
ColN1 ColN2 ColN3 ... ColN10

tblTemp has data like this

ColN1      N1Value
ColN2      N2Value
ColN3      N3Value
ColN4      N4Value
....
ColN10      N10Value

and you want to insert a row in tblStatic
N1Value   N2Value....N10Value

if this is what you are looking for
you can make use of pivot operator, or open a cursor and insert/update a new record.

using pivot

insert into tblStatic([ColN1], [ColN2], [ColN3], [ColN4],... [ColN10])
SELECT
*
FROM
(SELECT *
FROM tblTemp) p
PIVOT
(
MAX( tlbStatic_field_value )
FOR tblStatic_field_name IN
( [ColN1], [ColN2], [ColN3], [ColN4],... [ColN10])
) AS pvt

appari: this is what I need... let me try this and see what happens...
Lowfatspread: why would I need a sequence col?

appari: what are tlbStatic_field_value  and tlbStatic_field_name?

from yopur question
>>tblTemp has 2 cols: [tblStatic_field_name], [tlbStatic_field_value]

thanks - clearly i'm not paying attention...

can you explain the MAX in the select statement?

for pivot operator to work you need to use some aggregate function. here i used max.

ok thanks again...

do the field names and values by row need to be in the same order as the cols appear in the static table? sorry to ask so many stupid questions but the pivot table thing is new to me...

i'm getting two errors:
Msg 8117, Level 16, State 1, Line 2
Operand data type text is invalid for max operator.
Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

is tlbStatic_field_value type is text?

can you post the actual SQL you are executing?

it helps if you can post the structres of both the tables

ok, here is the temp table with the two cols:

x_mapping: 3 cols
doc_id         varchar(30)    ---- > this tells me which document id I want to dump to the other table
table_name varchar(255)
value           text

tblStatic (sample only since contains over 350 fields):
Application        int
dtTimeStamp   datetime
....

SQL statement:
INSERT INTO [svcdb].[dbo].[tblStatic]
           ([Application]
           ,[dtTimeStamp]
          '.....)
SELECT * FROM (SELECT * FROM x_mapping where doc_id='29557') p
PIVOT
(MAX( [value] ) FOR table_name IN
           ([Application]
           ,[dtTimeStamp]
           , ......)
) AS pvt

you cannot use text field with pivot.
is it possible to change x_mapping tables value field to something like varchar(255)?

yes, I can do that...

change it and try again

and change sql to

INSERT INTO [svcdb].[dbo].[tblStatic]
           ([Application]
           ,[dtTimeStamp]
          '.....)
SELECT ([Application]
           ,[dtTimeStamp]
           , ...... FROM (SELECT * FROM x_mapping where doc_id='29557') p
PIVOT
(MAX( [value] ) FOR table_name IN
           ([Application]
           ,[dtTimeStamp]
           , ......)
) AS pvt

Now I get:
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

for the timestamp cols...

should i just cast them to varchar?

do you have timestamp type column? i think timestamp column cannot be updated, try removing it from insert column list.

ok, this time it started to run but i got:
String or binary data would be truncated.

I guess because some fields are larger than the values held in the temp table...

most of the fields are nvarchar(50) in tblStatic however there is one ntext field...
the [value] field in the temp table is nvarchar(8000)
what's being truncated?

try running select alone and see which column is getting data larger than allowed size.
or try
 INSERT INTO [svcdb].[dbo].[tblStatic]
           ([Application]
           ,[dtTimeStamp]
          '.....)
SELECT ([Application]
           ,[dtTimeStamp]
           , ...... FROM (SELECT * FROM x_mapping where doc_id='29557') p
PIVOT
(MAX( rtrim([value] )) FOR table_name IN
           ([Application]
           ,[dtTimeStamp]
           , ......)
) AS pvt

when i add the rtrim() I get an error with the parenthesis, but they match?

i have another question now... when I execute only the select part of the statement I get 300+ rows instead of one row which is what I want? It seems it's basically creating a grid of values x values and they are all null... something isn't right here.

what is the other solution not involving a pivot table:
"if this is what you are looking for
you can make use of pivot operator, or open a cursor and insert/update a new record."

without a sequence id i didn;t think you could arrange the columns to match the table structure...

well at this point I'll take any advice. the previous answer is not working for me. to reiterate what I need (as defined by appari):

tblStatic has columns
ColN1 ColN2 ColN3 ... ColN10

tblTemp has data like this

docid1      ColN1      N1Value
docid1      ColN2      N2Value
docid1      ColN3      N3Value
docid1      ColN4      N4Value
....
docid1      ColN10      N10Value

and you want to insert a row in tblStatic
N1Value   N2Value....N10Value for docid1

Can someone please help? this is urgent...

i think i'll take your solution and split it into xxx number of insert statements.