set variable to be a field value from another table

[ddeegan]

Hello

I want to insert a field from one table into another table.

Something like...

declare @s_number int
set @s_number= select max(FIELD1)from TABLE1

select "truck", @s_number
INTO TABLE2

I know the syntax is incorrect, the idea is to take a value from TABLE1 and insert it into TABLE2 with other values. @s_number could be different every time.

Thanks for any help
Dave

 

[vongrunt]

insert into Table2( col1, col2 )
select 'truck',
   (select max(field1) from Table1)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ddeegan]

Thank you - now I want to combinbe that with
using IDENTITY()

I am trying to import records into a third party software. Every table in the db has a column named SEQUENCE_NO. This column is populated with the last # used - stored in a table named APP_NEXT_NUMBER

APP_NEXT_NUMBER has a column for table and one for last sequence number used

If I want to import records from an outside source, I need to assign the sequential number myself. I did this before with IDENTITY() by using "SELECT INTO" then INSERT into the destination table (thanks to help from here)

I want to make the process more automated - now I find the last # from APP_NEXT_NUMBER and paste it into the statement

This is what I currently have (works, but # needs to be hard coded)
select
FIELD1,
FIELD2,
identity(int,2464262,1) as sequence_no
into TEMP_TABLE
from SOURCE_TABLE

I would like to assign this value

declare @s_number int
set @s_number= select max(SEQUENCE_NO)
from APP_NEXT_NUMBER
where TABLENAME='History'

select
FIELD1,
FIELD2,
identity(int,@s_number,1) as sequence_no
into TEMP_TABLE
from SOURCE_TABLE

Would it be easier to use FETCH in a cursor? That is a new direction I am looking into - identity() only seems to work with INT and the seed is 7,921,336 if I try to use a varable (no matter what the variable is assigned to, the # is always 7,921,336)

Thanks for your help

Dave