April 21, 2008 at 11:53 am
I am running into and issue with a temp table. The temp table is created using a Select INTO command but evidently some of columns from the base table have certain columns that do not allow NULLS. Is there a way I can have all the columns in the temp table to allow NULLS?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 21, 2008 at 11:55 am
Sure,
Create the Temp table with a CREATE TABLE
and then do an INSERT INTO instead of SELECT INTO
Dave Novak
April 21, 2008 at 12:09 pm
I created the Temp Table but how would I change my current code into the INSERT Statement:
Current COde:
Select 'OrigConsID' = @ConsultantID
,(Select Top 1 FirstName + ' ' + LastName FROM Consultant d
WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'
,D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.EffectiveDate
,(SELECT Top 1 b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON D.SponsorID = B1.ConsultantID) AS SponsorName
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,D.ConsultantXID
,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @StartPeriodDate And @EndPeriodDate),0) AS SalesVolumeTotal
,D.Active
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,ISNULL(r.RepFlag,' ')AS RepFlag
,D.StatusID
INTO #DLFiltered from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 3
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,D.CurrentLevelAchieveDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,D.StatusID
,R.RepFlag
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 21, 2008 at 12:15 pm
Instead of:
SELECT {list of fields}
INTO {temp table}
FROM ....
try:
INSERT INTO {temp table} ({list of fields from the temp table})
SELECT ...
FROM ....
Dave
April 21, 2008 at 12:16 pm
insert into tablename (column list)
select column list
from ...
For more details look in BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1054c76e-0fd5-4131-8c07-a6c5d024af50.htm)
April 21, 2008 at 12:21 pm
remove the INTO statement, and then put an INSERT clause in front of your select.
As in
INSERT #DLFiltered (
OrigConsID,
OrigConsName
,ConsultantID
,Downlinelevel
,ConsultantName
,LastName
,BillToAddressLine1
,BillToAddressLine2
,BillToCity
,BillToState
,BillToZip
,HomePhone
,BusinessPhone
,OtherPhone
,Fax
,EmailAddress
,EffectiveDate
,SponsorName
,SponsorID
,AchievedTitle
,CurrentLevelXID
,AchieveDate
,ConsultantXID
,SponsorXID
,SalesVolumeTotal
,Active
, AgreementDate
,RepFlag
,StatusID
)
Select 'OrigConsID' = @ConsultantID
,(Select Top 1 FirstName + ' ' + LastName FROM Consultant d
WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'
,D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.EffectiveDate
,(SELECT Top 1 b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON D.SponsorID = B1.ConsultantID) AS SponsorName
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,D.ConsultantXID
,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @StartPeriodDate And @EndPeriodDate),0) AS SalesVolumeTotal
,D.Active
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,ISNULL(r.RepFlag,' ') AS RepFlag
,D.StatusID
from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 3
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,D.CurrentLevelAchieveDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,D.StatusID
,R.RepFlag
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 21, 2008 at 3:20 pm
This works great but I am having issue with my Filter statement:
CREATE TABLE #DLFiltered(
[OrigConsID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrigConsName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsultantID] [nchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Downlinelevel] [int] NULL,
[ConsultantName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToAddressLine1] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToAddressLine2] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToCity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToState] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToZip] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BusinessPhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OtherPhone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveDate] [datetime] NULL,
[SponsorName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SponsorID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AchievedTitle] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrentLevelXID] [int] NULL,
[AchieveDate] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConsultantXID] [int] NULL,
[SponsorXID] [int] NULL,
[SalesVolumeTotal] [decimal](38, 2) NULL,
[Active] [bit] NULL,
[AgreementDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RepFlag] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #DLFiltered (
OrigConsID,
OrigConsName
,ConsultantID
,Downlinelevel
,ConsultantName
,LastName
,BillToAddressLine1
,BillToAddressLine2
,BillToCity
,BillToState
,BillToZip
,HomePhone
,BusinessPhone
,OtherPhone
,Fax
,EmailAddress
,EffectiveDate
,SponsorName
,SponsorID
,AchievedTitle
,CurrentLevelXID
,AchieveDate
,ConsultantXID
,SponsorXID
,SalesVolumeTotal
,Active
, AgreementDate
,RepFlag
,StatusID
)
Select 'OrigConsID' = @ConsultantID
,(Select Top 1 FirstName + ' ' + LastName FROM Consultant d
WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'
,D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.EffectiveDate
,(SELECT Top 1 b1.FirstName + ' ' + b1.LastName FROM dbo.consultant
LEFT OUTER JOIN dbo.uvwConsultantDownLine AS B1 ON D.SponsorID = B1.ConsultantID) AS SponsorName
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,D.ConsultantXID
,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ON
V.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))
AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @MonthStartDt And @MonthEndDt),0) AS SalesVolumeTotal
,D.Active
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,ISNULL(r.RepFlag,' ') AS RepFlag
,D.StatusID
from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 3
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.LastName
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.OtherPhone
,D.Fax
,D.EmailAddress
,D.SponsorName
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelXID
,D.CurrentLevelAchieveDate
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,D.StatusID
,R.RepFlag
IF @LineFilter = ('Only Line 1')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
ELSE -- I am not sur ehow to do the insert statment here.
IF @LineFilter = ('Only Line 2')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
IF @LineFilter = ('Only Line 3')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 3
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
IF @LineFilter = ('Lines 1 and 2')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 2
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
IF @LineFilter = ('Lines 1, 2 and 3')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel Between 1 AND 3
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
--
DROP TABLE #Downline
DROP TABLE #DLFiltered
DROP TABLE #Temp
DROP TABLE #Temp2
What needs to happen Is I need to insert the consultantID and ConsultantName if the record set comes back. Now that I think about it there should never be a empty recordset because I need one row that has the consultantid and ConsultantName.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 22, 2008 at 7:26 am
Not sure just what you are asking about the 'Filters'.
Are you trying to insert the select statement from each If block into #DLFiltered, insert the select statement into another table, are you wanting to insert something totally different?
What results do you want to happen after the initial create temp table and insert into said temp table?
Dave
April 22, 2008 at 7:31 am
Art, it seems to me you could include your filter statements in the Where clause of the insert, and then select the whole table. That would save the database a bunch of work, since it wouldn't be filling the temp table with a bunch of data that won't be used.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 10:27 am
What I expect to see would be that in the If Statement did not return any rows then a insert would happen into the table and then that row would be returned with a single row containing just the consultantID and ConsultantName.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 22, 2008 at 10:49 am
Art,
IF @LineFilter = ('Only Line 1')
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDt AND @MonthENDDt
Take this first filter, I am assuming the if nothing is returned by the SELECT statement, then you want to insert something into #DLFiltered temp table. What is the something or am I way off base?
If you are wanting to find out if there is a record that matches your where criteria, then try this after your IF @LineFilter = 'Only Line 1'
IF (SELECT COUNT(*) FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndDt) > 0
BEGIN{Put your insert statement here followed by your select statement to return the ConsultantId and ConsultantName}
END
Dave Novak
April 22, 2008 at 1:40 pm
Don't use "if (select count(*)...) > 0". Use "if exists (select 1 ...)". Performs better. Less steps for the server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 1:46 pm
GSquared is right, the if exists clause is better.
this would look like this:
IF EXISTS (SELECT NULL FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndDt)
BEGIN{Put your insert statement here followed by your select statement to return the ConsultantId and ConsultantName}
END
Dave Novak
April 22, 2008 at 2:30 pm
Doing it this way how does it know what filter to use?
IF EXISTS (SELECT NULL FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel = 1
AND EffectiveDate BETWEEN @MonthStartDT AND @MonthEndDt)
BEGIN{Put your insert statement here followed by your select statement to return the ConsultantId and ConsultantName}
END
The
IF @LineFilter = ('Only Line 1')
The @LineFilter is being passed in as a paramater.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 22, 2008 at 2:35 pm
Make the IF statement as follows:
IF @LineFilter = ('Only Line 1')
AND EXISTS (SELECT NULL FROM .... WHERE .......)
BEGIN {Insert statement and select statement}
END
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply