francecap
asked on
insert into select from... with modification
I want operate a copy data between two table of different DB, were the table are similar bat not identically, and I have to do data validation before insert data.
The query below is my prototype, bat query analyzer return error:The name 'ditta' is not permitted in this context...
My idea is to modify data in select statement as insert table require.
this is the query --------------------------
use dbcamp
INSERT INTO ITAC.dbo.ITAC02_TESTATACOS TI(ITAC02_ DITTA_CG18 , ITAC02_CODCAMP_ITAC01, ITAC02_CODSTAB_ITMG02, ITAC02_TIPOCF_CG44, ITAC02_CLIFOR_CG44, ITAC02_INDSTATO, ITAC02_PREZZOCAPO, ITAC02_PREZZOKG, ITAC02_FLGPREZZOMOD, ITAC02_FLGCOSTIDET)
VALUES(Q1.ditta, Q1.CodCampione, Q1.CodStabilimento, Q1.TipoClienteFornitore, Q1.CodiceCliente, Q1.IndStato, 0, 0,0,1)
SELECT
1 as ditta,
CodiCamp as CodCampione,
'ITA' as CodStabilimento,
0 as TipoClienteFornitore,
-- Codice cliente, se null o 0, imposta 11000 cliente generico
case
when (CodCliente is null) then
11000
when (CodCliente =0) then
11000
else
CodCliente
end as CodiceCliente,
3 as IndStato
FROM dbcamp.dbo.ListCamp as Q1
where (CodiCamp IS not NULL) and cast(CodiCamp as int)<31000
The query below is my prototype, bat query analyzer return error:The name 'ditta' is not permitted in this context...
My idea is to modify data in select statement as insert table require.
this is the query --------------------------
use dbcamp
INSERT INTO ITAC.dbo.ITAC02_TESTATACOS
VALUES(Q1.ditta, Q1.CodCampione, Q1.CodStabilimento, Q1.TipoClienteFornitore, Q1.CodiceCliente, Q1.IndStato, 0, 0,0,1)
SELECT
1 as ditta,
CodiCamp as CodCampione,
'ITA' as CodStabilimento,
0 as TipoClienteFornitore,
-- Codice cliente, se null o 0, imposta 11000 cliente generico
case
when (CodCliente is null) then
11000
when (CodCliente =0) then
11000
else
CodCliente
end as CodiceCliente,
3 as IndStato
FROM dbcamp.dbo.ListCamp as Q1
where (CodiCamp IS not NULL) and cast(CodiCamp as int)<31000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
50/50 split me and Hill, is my suggestion. We both have submitted valid but slightly different solutions.
INSERT INTO ITAC.dbo.ITAC02_TESTATACOS
VALUES(1, Q1.CodCampione, Q1.CodStabilimento, Q1.TipoClienteFornitore, Q1.CodiceCliente, Q1.IndStato, 0, 0,0,1)
SELECT
CodiCamp as CodCampione,
'ITA' as CodStabilimento,
0 as TipoClienteFornitore,
-- Codice cliente, se null o 0, imposta 11000 cliente generico
case
when (CodCliente is null) then
11000
when (CodCliente =0) then
11000
else
CodCliente
end as CodiceCliente,
3 as IndStato
FROM dbcamp.dbo.ListCamp as Q1
where (CodiCamp IS not NULL) and cast(CodiCamp as int)<31000