What you're looking for is " Select Into ".

The short answer is you can't without using INTO clause on your SELECT. There are potential issues dealing with nullable values and potentially the need to cast datatypes if additional records will be added later.

You can however wrap the create and insert statements into a stored procedure. Also depending on your needs you could either do a conditional create (only create the table if it doesn't exist) and append new records or a conditional delete (drop table if it exist), create table, insert records. I prefer the latter to create idempotent logic (i.e. spRecreateProductCodeLookup).

You could use Table Value Constructor to define your insert values and then select into as others have mentioned to insert it directly to your table like this:

SELECT *
       INTO productcodes
  FROM (VALUES(123,456,1234),(425,35,5451))as A(test,test1,test2)

Here is a demo on this: SQL Fiddle

you could use following query example to CREATE AND INSERT in the same time SQL SERVER :

DECLARE 
    fc_name               VARCHAR2(100 CHAR); 
    cp_productid          VARCHAR2(255 CHAR); 
    cp_productname        VARCHAR2(50 CHAR); 
    cp_consumerproduct_id NUMBER(19, 0); 
    ff_fielduniquename    VARCHAR2(300 CHAR); 
BEGIN 
    EXECUTE IMMEDIATE ('CREATE TABLE FIELD_FIELDCONTAINER (
       NAME VARCHAR2(100 CHAR) NOT NULL ENABLE,                                                                 
       FIELDUNIQUENAME VARCHAR2(300 CHAR) NOT NULL ENABLE,                                                                 
       CONSTRAINT UK_FUV6CPWSPOMLJORF3DT3L78VY UNIQUE (NAME,FIELDUNIQUENAME) 
          ENABLE )'); 

    FOR i IN (SELECT fc.name, 
                     fld.fielduniquename 
              FROM   field fld, 
                     fieldcontainer fc, 
                     fields_fieldcontainer ff 
              WHERE  fld.id = ff.field_id 
                     AND fc.id = ff.fieldcontainer_id) LOOP 
        fc_name := i.name; 

        ff_fielduniquename := i.fielduniquename; 

        EXECUTE IMMEDIATE 
          'INSERT INTO FIELD_FIELDCONTAINER (NAME,FIELDUNIQUENAME) values 
              (:1 ,:2)' 

USING fc_name, ff_fielduniquename; 
END LOOP; 

COMMIT; 
END;

1.While creating and inserting into a table is not quite a good practice and you might not see that in real world scenarios(I haven't seen it yet). Nevertheless here is the syntax for doing so.

SELECT * INTO productcodes
FROM (VALUES(1,124,'Pringles'),
        (2,311,'Skittles'),
        (3,537,'Kitkat'))
AS C(Id,ProductCode,ProductName)
SELECT * FROM productcodes

If you execute the above T-SQL statement as a single statement you will get something like this.Remember this will create a physical table named productcodes in the object explorer because of the INTO clause. Notice that I must alias the TVC (Table Valued Constructor) just like a derived table. I also have to provide column names right after the alias.

2.You can also create and insert into table without creating the physical table without the use of the INTO clause as below:

SELECT * FROM 
(VALUES
   (101, 'Pepsi'),
   (102, 'Fanta'),
   (103, 'Coca-Cola')
) 
AS Product(Code,Name);

3.You can also achieve this via a hack using the string_split() function.In that case, the product codes won't be integers anymore but strings. But again this is just a hack & not a good approach.

SELECT Value AS Code INTO ProductCodes FROM string_split('124,256,311,768,537',',')
SELECT * FROM ProductCodes