order id based on date

I want to create an orderid based on the id and date like 2011070001

Code:

DECLARE @TT TABLE (
orderid as CONVERT(varchar, YEAR(date)) + CONVERT(varchar, MONTH(date)) + RIGHT('000' + CONVERT(varchar, id),5),
id int IDENTITY(1,1),
date date
)

INSERT INTO @TT VALUES ('2011-06-11 08:43:17.000')
INSERT INTO @TT VALUES ('2011-07-2开发者_如何学编程0 08:43:17.000')

SELECT * FROM @TT

OUTPUT

order             id     date

201160001   1      2011-06-11

201170002    2     2011-07-20

How to insert this temp table into my order table?

just write a insert statement above of your select. But you have to make sure that the columns returning from your select matches the insert columns.

DECLARE @TT TABLE (
orderid as CONVERT(varchar, YEAR(date)) + CONVERT(varchar, MONTH(date)) + RIGHT('000' + CONVERT(varchar, id),5),
id int IDENTITY(1,1),
date date
)

INSERT INTO @TT VALUES ('2011-06-11 08:43:17.000')
INSERT INTO @TT VALUES ('2011-07-20 08:43:17.000')

INSERT INTO ORDERTABLE
SELECT * FROM @TT