Re: INSERT Problem

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 17:01:34 +0100

forget the

OR rt.Col3 IS NULL

in the INSERT. My hand got carried away. 

-- 
-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:ehpYvuLdEHA.1424@tk2msftngp13.phx.gbl...
> Ok
>
> Let me offer an example
>
> CREATE TABLE Staging(col1 int, col2 int, col3 int, primary key(col1,col2))
> CREATE TABLE RealTable(col1 int, col2 int, col3 int, primary
key(col1,col2))
> INSERT Staging VALUES(1,2,3)
>
> INSERT RealTable(col1,col2,col3)
> SELECT s.col1,s.col2,s.col3
> FROM Staging s LEFT OUTER JOIN RealTable rt
> ON s.col1 = rt.col1 AND s.col2 = rt.col2
> WHERE rt.col1 IS NULL OR rt.col2 IS NULL OR rt.Col3 IS NULL
>
> INSERT Staging Values(1,3,2)
>
>
> INSERT RealTable(col1,col2,col3)
> SELECT s.col1,s.col2,s.col3
> FROM Staging s LEFT OUTER JOIN RealTable rt
> ON s.col1 = rt.col1 AND s.col2 = rt.col2
> WHERE rt.col1 IS NULL OR rt.col2 IS NULL OR rt.Col3 IS NULL
>
> SELECT * FROM RealTable
>
>
>
> -- 
> -- 
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
>
> "Gustavo" <anonymous@discussions.microsoft.com> wrote in message
> news:5c9b01c474ba$20b46990$a301280a@phx.gbl...
> > Hi All(an):
> > As I was advised I created this query:
> >
> > USE ipd
> >
> > INSERT INTO dbo.OrderRegulation
> > (TitleId,YearId, MyIssue, RetId, IssueId, Location, Draw,
> > Reorder, [Returns], Cost, Price)
> >
> > SELECT StagingOR.TitleId, StagingOR.YearId,Stagin
> > gOR.MyIssue, StagingOR.RetId, StagingOR.IssueId,
> > StagingOR.Location, StagingOR.Draw,
> >                       StagingOR.Reorder, StagingOR.
> > [Returns], StagingOR.Cost, StagingOR.Price
> > FROM StagingOR LEFT OUTER JOIN
> >                       OrderRegulation ON
> > StagingOR.TitleId = OrderRegulation.TitleId AND
> > StagingOR.YearId = OrderRegulation.YearId AND
> >                       StagingOR.MyIssue =
> > OrderRegulation.MyIssue AND StagingOR.RetId =
> > OrderRegulation.RetId AND
> >                       StagingOR.IssueId =
> > OrderRegulation.IssueId
> > WHERE     (dbo.OrderRegulation(StagingOR.TitleId,
> > StagingOR.YearId, StagingOR.MyIssue, StagingOR.RetId,
> > StagingOR.IssueId) IS NULL)
> >
> >
> >
> >
> >
> >
> >  that keeps returning the following error:
> >
> > Server: Msg 208, Level 16, State 1, Line 4
> > Invalid object name 'dbo.OrderRegulation'.
> >
> > I am 1000% sure that the table exists and is not
> > mispelled, the query syntax checks ok and I still get the
> > error.
> > What is wrong??
> > Thanks in advance
> > Gustavo
> >
>
>

Relevant Pages

  • Re: INSERT Problem
    ... >>Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >>> INSERT Staging VALUES ...
    (microsoft.public.sqlserver.dts)
  • Re: Disabling DTS Bulk insert task
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Dynamically set up DTS Transformations?
    ... > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)> www.SQLDTS.com - The site for all your DTS needs. ... >> package each time but set the source table and destination text file ...
    (microsoft.public.sqlserver.dts)
  • Re: bcp
    ... >>> How to manipulate the Execute Process task. ... >>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Trigger Current Row ID
    ... Have you seen the INSERTED and DELETED tables available to you in a trogger. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
Loading