June 17, 2009 at 2:21 pm
I want to insert a whole chunk of records at one time, based on some data being pulled from another table. The table I'm inserting into has a sequence field that I want to just populate in the order I insert the records but I can't quite remember how to do this.
my basic insert statement is:
Insert option_answers
SELECT 'SPC-'+OptionGroup+'-'+RTrim(cast(pid as varchar(6))),
'NEM','SPC-'+OptionGroup,sequence_number,Laminate,upcharge,NULL
FROM vw_NSP_LAMINATES
WHERE OptionGroup <> 'LA'
the sequence_number field is the one I am trying to figure out how to populate with 1,2,3,4...etc It is not an actual field in the vw_NSP_Laminates view. I'm thinking there is a variable or something in SQL Server that will do what I am asking but I'm drawing a blank and not being able to find the way to do this...
Anyone able to help me out?
Thanks!
MJ
~mj
June 17, 2009 at 2:28 pm
Check out row_number() function. I think it is what you are looking for.
Or you can add an identity column to the option_answers table.
June 17, 2009 at 2:33 pm
The ROW_NUMBER() fucntion is probably the way to go, but if you have the option to create the table you can use an IDENTITY INSERT INTO:SELECT 'SPC-'+OptionGroup+'-'+RTrim(cast(pid as varchar(6))),
'NEM','SPC-'+OptionGroup,IDENTITY(INT,1,1) as seq_number,Laminate,upcharge,NULL
INTO option_answers
FROM vw_NSP_LAMINATES
WHERE OptionGroup 'LA'Here is a ROW_NUMBER() example:Insert option_answers
SELECT 'SPC-'+OptionGroup+'-'+RTrim(cast(pid as varchar(6))),
'NEM','SPC-'+OptionGroup,
ROW_NUMBER() OVER (ORDER BY OptionGroup) AS RowNum
,Laminate,upcharge,NULL
FROM vw_NSP_LAMINATES
WHERE OptionGroup 'LA'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply