Hello

The question is simple, msdn did not help much, show on fingers: a CTE query returns a table with values ​​that I need to insert into another table, but! , you need to insert not all fields but only certain ones. It is necessary to make all this one

  • What does it mean to make one select, i.e. to sample and insert per query? - sp7
  • one
    And what is the problem, actually? INSERT allows you to specify fields. - Pavel Mayorov
  • example: WITH CTE (field1, field2, field3, field4) (declare @ShiftNo varchar (10) set @ ShiftNo = (Select MAX (shiftNo) From Rus .. [tbl.transakcija]) - get the last shift, then sort through the data ) - here is my data table, then INSERT INTO Rus .. [tbl.inkassacija] of certain fields FROM CTE - Kawalski

1 answer 1

Is it something like that?

CREATE TABLE #t (f1 INT, f2 INT); WITH CTE (fld1, fld2, fld3) AS ( SELECT 1, 2, 3 UNION ALL SELECT 4, 5, 6 UNION ALL SELECT 7, 8, 9 ) INSERT INTO #t SELECT fld1, fld3 FROM CTE; SELECT * FROM #t; -- 1, 3 -- 4, 6 -- 7, 9 DROP TABLE #t;
  • yes, but without a temporary shelter. ie, in CTE, you need to take data from table1, for example, fields a, c, e, f and transfer them to table2 in fields a, b, c, d - Kawalski
  • it looks like it is necessary to align the CTE table by the number of fields and by their sequence to the inserted one, and then simple insert from CTE - Kawalski
  • one
    The temporary table here is purely for illustration. Well and nothing prevents to specify the necessary fields in the necessary order: INSERT INTO #t (f2, f1) SELECT fld3, fld1 FROM CTE - Yaant