SQL insert into select auto increment variable

Viewed 9472

I am a newbie in SQL. Please bear with me. Thanks

I wanna to insert records into a table name t1 where some of the fields in those records are from temp table @t2.

create table t1
(
    transid             varchar(15) not null primary key,
    content             varchar(1000),
    userid              int not null,
    dtcreate            datetime not null,
)

DECLARE @seqno INT = 1;

DECLARE @t2 TABLE ( userid INT, content VARCHAR(1000), dtcreate DATETIME)

Let's say i have 10 records in @t2

I want to insert into t1 for all the records in @t2(temp table)

INSERT INTO t1 (transid,content,userid,dtcreate)
SELECT (CONVERT(VARCHAR(10), dtcreate, 112)+RIGHT('0000'+CONVERT(VARCHAR, ( 
@seqno =+ 1)), 5)) , content, userid, dtcreate FROM @t2

This is where the @seqno =+ 1 does not allow me to do that. How can i do it if i want something like this? Thanks guys. I'm a super newbie.

5 Answers
Related