asked on

Could you point a way to save a select result in a new table by using MSSQLServer?

Hi Experts

Could you point a way to save a select result in a new table by using MSSQLServer?

Accordingly to:

My attempts (no success)

Select * Into MC1_CityZipCodeExt_LAB_NOVO  -- new table to receive the results
FROM
SELECT
  y.lIdState  as lIdState, --  lIdState
  LOC_NU,  --  cIdCity
  LOC_NO,  --  cNameCity
  UFE_SG,  --  cCodeState
  CEP,      -- cZiCode
  1 as mc1Enabled,        -- mc1Enabled
  CURRENT_TIMESTAMP as mc1LastUpdate, --mc1LastUpdate
  'novo' as condicao
FROM LOG_LOCALIDADE_OK t
LEFT JOIN MC1_StateZipCodeExt y ON  t.UFE_SG = y.cState
WHERE NOT EXISTS (SELECT NULL
                  FROM MC1_CityZipCodeExt x
              WHERE x.cIdCity = t.LOC_NU
              AND   x.cCodeState = t.UFE_SG
              AND   x.cZiCode = t.CEP
                  )
AND t.CEP IS NOT NULL

Open in new window

and

SELECT
  y.lIdState  as lIdState, --  lIdState
  LOC_NU,  --  cIdCity
  LOC_NO,  --  cNameCity
  UFE_SG,  --  cCodeState
  CEP,      -- cZiCode
  1 as mc1Enabled,        -- mc1Enabled
  CURRENT_TIMESTAMP as mc1LastUpdate, --mc1LastUpdate
  'novo' as condicao
FROM LOG_LOCALIDADE_OK t
LEFT JOIN MC1_StateZipCodeExt y ON  t.UFE_SG = y.cState
WHERE NOT EXISTS (SELECT NULL
                  FROM MC1_CityZipCodeExt x
              WHERE x.cIdCity = t.LOC_NU
              AND   x.cCodeState = t.UFE_SG
              AND   x.cZiCode = t.CEP
                  )
AND t.CEP IS NOT NULL
Into MC1_CityZipCodeExt_LAB_NOVO

Open in new window

Thanks in advance

Jim Horn

Define 'no success'.

Did it throw an error, and if so what was an error message?
Did it execute successfully but not create table MC1_CityZipCodeExt_LAB_NOVO ?
Did it execute successfully, create the table, but there is no data in the table?
Are you looking in the correct database (see database combo box in top left of screen for the database where this executed) and schema?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eduardo Fuerte

ASKER

Hi

Very good attempt, it worked out!

Eduardo Fuerte

ASKER

Jim

Thank you for the solution!