Insert Problem Using Select Statement SP

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 2000 Forums

Transact-SQL (2000)

Insert Problem Using Select Statement SP

Author

Topic

opusid
Starting Member

4 Posts

Posted - 2007-11-23 : 08:04:24

Hi Guys

Need your help.

I am using a Select Statement to populate a Temp Table as part of a stored proceedure. I have this working fine, however I have one field in the Temp Table which I need to populate using a var called @TableName which is defined in the Stored Proceedure.

So, I can popluate every field in the new Temp Table from the Select Statement but I need to add the value of @TableName to the remaining field in Temp Table.

Any ideas as Im completely stuck

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-23 : 08:41:25

Is this?

Select columns, @TableName as TableName from #temptable

Madhivanan

Failing to plan is Planning to fail

opusid
Starting Member

4 Posts

Posted - 2007-11-23 : 08:45:42

Hiya

Heres the full code.

CREATE PROCEDURE dbo.TAT_Advert_Renewal

@StartDate varchar(40),
@EndDate varchar(40)

AS

CREATE TABLE #TAT_Renewal_Results

(
PrimerID int IDENTITY(1,1) NOT NULL Primary Key,
ProfileID char(10),
MembershipStatus char(10),
CompanyName char(100),
TraderID char(4),
CompanyContactFirstName char(100),
BillingAddress1 char(100),
CompanyContactLastName char(100),
BillingAddress2 char(100),
BillingDistrict char(100),
BillingCity char(100),
BillingCounty char(100),
BillingPostcode char(20),
BillingEmail char(128),
AdvertRenewalPrice char(10),
AdvertRenewalDate smalldatetime NOT NULL ,
TableName char(30)
)

DECLARE @TableName varchar(100)

DECLARE MyCur INSENSITIVE CURSOR FOR

SELECT BusinessTypeTableName
FROM dbo.TAT_Business_Table_Name

OPEN MyCur

WHILE 1 = 1
BEGIN
FETCH NEXT FROM MyCur INTO @TableName
IF @@fetch_status <> 0
BREAK

declare @sql varchar(4000)

SELECT @sql='INSERT INTO #TAT_Renewal_Results (ProfileID,MembershipStatus,CompanyName,TraderID,CompanyContactFirstName,BillingAddress1,CompanyContactLastName,BillingAddress2,BillingDistrict,BillingCity,BillingCounty,BillingPostcode,BillingEmail,AdvertRenewalPrice,AdvertRenewalDate,TableName)
'
SELECT @sql = @sql+' SELECT dbo.TAT_'+@TableName+'.ProfileID, dbo.TAT_Traders.membershipStatus, dbo.TAT_Traders.companyName, dbo.TAT_'+@TableName+'.TraderID, '

SELECT @sql = @sql +'dbo.TAT_Traders.companyContactFirstname, dbo.TAT_Traders.billingAddress1, dbo.TAT_Traders.companyContactLastname,'

SELECT @sql = @sql +'dbo.TAT_Traders.billingAddress2, dbo.TAT_Traders.billingDistrict, dbo.TAT_Traders.billingCity, dbo.TAT_Traders.billingCounty,'

SELECT @sql = @sql +'dbo.TAT_Traders.billingPostcode, dbo.TAT_Traders.billingEmail, dbo.TAT_'+@TableName+'.AdvertRenewalPrice, '

SELECT @sql = @sql +'dbo.TAT_'+@TableName+'.AdvertRenewalDate '

SELECT @sql = @sql +'FROM dbo.TAT_'+@TableName+' LEFT OUTER JOIN '

SELECT @sql = @sql +'dbo.TAT_Traders ON dbo.TAT_'+@TableName+'.TraderID = dbo.TAT_Traders.traderID '

SELECT @sql=@sql+'WHERE (dbo.TAT_Traders.membershipStatus =1)'

exec (@sql)

End

DEALLOCATE MyCur

SELECT AdvertRenewalDate, AdvertRenewalPrice, BillingEmail,PrimerID,ProfileID,MembershipStatus,CompanyName,TraderID,CompanyContactFirstName,BillingAddress1,CompanyContactLastName,BillingAddress2,BillingDistrict,BillingCity,BillingPostcode,BillingCounty,TableName

FROM #TAT_Renewal_Results

WHERE (AdvertRenewalDate BETWEEN CONVERT(DATETIME, ''+@startdate+'', 102) AND CONVERT(DATETIME, ''+@enddate+'', 102))

ORDER BY AdvertrenewalDate ASC

DROP TABLE #TAT_Renewal_Results
GO

opusid
Starting Member

4 Posts

Posted - 2007-11-23 : 08:51:28

Hi Guys

Now got the problem sorted - once again thanks for looking and helping.

Steve

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-23 : 08:57:25

SELECT @sql = @sql +'dbo.TAT_'+@TableName+'.AdvertRenewalDate, @TableName as TableName '

SELECT @sql = @sql +'FROM dbo.TAT_'+@TableName+' LEFT OUTER JOIN '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources