INSERT INTO with a WHERE clause problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT INTO with a WHERE clause problem

Author

Topic

Veritas5
Starting Member

6 Posts

Posted - 2009-08-05 : 14:32:12

Hello!

I need to tansfer drivers from one main drivers table to a secondary table that's going to be used for an application. My first transfert was an easy one. I used an INSERT INTO statement to get every single driver form my main table. Now here's my problem: I need to keep my secondary table up to date since new drivers are being constantly entered in our main table. I tried to use the same INSERT INTO statement with a Where clause so that only new drivers would be added into my secondary drivers table. However, it won't work and I keep getting a Wrong column name error... Here's my query, if somebody could tell me why it won't work. Thanks!

INSERT INTO attributionSql.dbo.chauffeur (noChauffeur, nomChauffeur, statut)
SELECT CR.NumeroDeChauffeur, CR.Nom, CR.Statut_Actif_Inactif_Exclus
FROM cis_win.cisadmin.ChauffeursRapid CR
WHERE CR.NumeroDeChauffeur <> noChauffeur

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 14:38:38

Is this what you want? insert chauffeurs that don't already exist?


INSERT INTO attributionSql.dbo.chauffeur (noChauffeur, nomChauffeur, statut)
SELECT CR.NumeroDeChauffeur, CR.Nom, CR.Statut_Actif_Inactif_Exclus
FROM cis_win.cisadmin.ChauffeursRapid CR
left outer join attributionSql.dbo.chauffeur x
       on x.noChauffeur = CR.NumeroDeChauffeur
where  x.noChauffeur is null

Be One with the Optimizer
TG

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-05 : 14:40:46

If your select statement cannot run on its own then the insert will not work.

Try this.

INSERT INTO attributionSql.dbo.chauffeur (noChauffeur, nomChauffeur, statut)
SELECT CR.NumeroDeChauffeur, CR.Nom, CR.Statut_Actif_Inactif_Exclus
FROM cis_win.cisadmin.ChauffeursRapid CR
left join attributionSql.dbo.chauffeur CH on CR.NumeroDeChauffeur = CH.noChauffeur
Where CH.noChauffeur is null

An infinite universe is the ultimate cartesian product.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-05 : 14:41:06

[code]
INSERT INTO attributionSql.dbo.chauffeur (noChauffeur, nomChauffeur, statut)
SELECT CR.NumeroDeChauffeur, CR.Nom, CR.Statut_Actif_Inactif_Exclus
FROM cis_win.cisadmin.ChauffeursRapid CR
WHERE NOT EXISTS (select * from attributionSql.dbo.chauffeur where CR.NumeroDeChauffeur = noChauffeur)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

natejax
Starting Member

9 Posts

Posted - 2009-08-05 : 14:41:11

try this:

INSERT INTO attributionSql.dbo.chauffeur (noChauffeur, nomChauffeur, statut)
SELECT CR.NumeroDeChauffeur, CR.Nom, CR.Statut_Actif_Inactif_Exclus
FROM
cis_win.cisadmin.ChauffeursRapid CR
LEFT JOIN
attributionSql.dbo.chauffeur CL
ON
CR.NumeroDeChauffeur = CL.noChauffeur
WHERE CL.statut is null

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 15:13:42

seems to be a distinct echo here...Is this a test to see how many people come up with the same solution?

Be One with the Optimizer
TG

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-05 : 15:32:49

quote:
Originally posted by TG

seems to be a distinct echo here...Is this a test to see how many people come up with the same solution?

Be One with the Optimizer
TG

I came so close to using X as my alias. That would have made it even better.

An infinite universe is the ultimate cartesian product.

Veritas5
Starting Member

6 Posts

Posted - 2009-08-05 : 16:30:02

Thanks guys! This works wonderfully! ;p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources