Insert Problem Using SP and SELECT Statement

Question

Insert Problem Using SP and SELECT Statement

Steve-159710

Valued Member

Points: 62
More actions
November 23, 2007 at 5:49 am

#62885

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 🙁

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Are you using dynamic SQL? If not, have you tried adding the parameter/variable to your SQL query whose results you are inserting?

E.g.:

CREATE PROC a ( @param INT )

AS

DECLARE @variable INT

SET @variable = 2

CREATE TABLE #temptable ( a INT, b int, c INT )

INSERT INTO #temptable VALUES ( 1, @param, @variable )

INSERT INTO #temptable select 1, @param, @variable

SELECT * FROM #temptable

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve-159710 Valued Member Points: 62 More actions · Answer 2

Hiya

thanks for responding. This is what I have below (very messy at the momment).

The value I am trying to Insert is: @TableName - it needs to go into field: LinkedTableName.

Hope this helps.

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,LinkedTableName)

'

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)

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

In the insert statement you are including in the () 16 column names, but you provide values only of 15 of them, try:

SELECT @sql='INSERT INTO #TAT_Renewal_Results (ProfileID,MembershipStatus,CompanyName,TraderID,CompanyContactFirstName,BillingAddress1,CompanyContactLastName,BillingAddress2,BillingDistrict,BillingCity,BillingCounty,BillingPostcode,BillingEmail,AdvertRenewalPrice,AdvertRenewalDate,LinkedTableName)

'

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, ''' + @TableName + ''' '

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)'

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve-159710 Valued Member Points: 62 More actions · Answer 4

Hiya

GREAT 🙂 - works a treat. It was always going to be something simple but when you have been looking at it for 3 hours you just cannot see the wood for the trees 🙂

Once again, thank you very muc and have a great weekend

Steve

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Needed a lot of formatting...(I just can't read the messed stuff)

And also the left join you've used is actually would turn into an inner join

because of where filter...which this can be elimited.

Here is a fully readable solution

DECLARE @strSQL VARCHAR(4000)

DECLARE @TableName VARCHAR(128)

SET @TableName = 'YourLinkedTable'

SET @strSQL= 'INSERT#TAT_Renewal_Results( ProfileID, MembershipStatus, CompanyName, '

+ 'TraderID, CompanyContactFirstName, BillingAddress1, CompanyContactLastName, '

+ 'BillingAddress2, BillingDistrict, BillingCity, BillingCounty, BillingPostcode, '

+ 'BillingEmail, AdvertRenewalPrice, AdvertRenewalDate, LinkedTableName ) '

+ 'SELECT T.ProfileID, TR.membershipStatus, TR.companyName, T.TraderID, '

+ 'TR.companyContactFirstname, TR.billingAddress1, TR.companyContactLastname,'

+ 'TR.billingAddress2, TR.billingDistrict, TR.billingCity, TR.billingCounty,'

+ 'TR.billingPostcode, TR.billingEmail, T.AdvertRenewalPrice, '

+ 'T.AdvertRenewalDate, ''' + @TableName + ''' '

+ 'FROMdbo.TAT_' + @TableName + ' T '

+ 'INNER JOIN dbo.TAT_Traders TR ON T.TraderID = TR.traderID '

+ 'AND TR.membershipStatus = 1 '

PRINT @strSQL

EXEC( @strSQL )

--Ramesh