Comments on Sagi Shkedy's Technical Blog: Temporary Tables and Dynamic SQL

A couple of questions: 1. SELECT * INTO #ins FROM...

2009-07-31T08:47:37.854-05:00

A couple of questions:
1.
SELECT * INTO #ins FROM INSERTED
SELECT * INTO #del FROM DELETED
This follows an update I am guessing - is the update running in the same execuution batch?
2.
Are you sending all your SQL code as one batch or are you sending it over several times over the same connection? If that is the case - the requests might come on a different connection.
Please let me know
If the latter is the case

Hi Sagi, I 'm facing the following issue. Inva...

2009-07-31T06:41:35.010-05:00

Hi Sagi,
I 'm facing the following issue.
Invalid object name '#ins'.
I don't get the above exception when running from the query explorer window. But i get this exception when executing from .NET Code. The following is my situation where i use dynamic sql:
SELECT * INTO #ins FROM INSERTED
SELECT * INTO #del FROM DELETED

SELECT @field = 0, @maxfield = MAX(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @CHAR = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @CHAR, 1) & @bit > 0 OR @Type in ('I', 'D')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') = @field
--Columns not to be audited
IF @fieldname NOT IN ('TransactionType', 'UserID', 'CreatedDate', 'LastModifiedBy', 'LastModifiedOn')
BEGIN
DECLARE @auditBody XML
DECLARE @ParmDefinition nvarchar(500)
--SELECT @sql = 'INSERT INTO ' + @AuditTableName + ' (ProgramCode, AttributeChanged, ChangedFrom, ChangedTo, ChangedDate, ChangedBy)'
SELECT @sql = 'SELECT @auditBodyOUTPUT = (SELECT COALESCE(i.ProgramCode, d.ProgramCode) AS ProgramCode '
SELECT @sql = @sql + ',''' + @fieldname + ''' AS AttributeChanged '
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),d.' + @fieldname + ') AS ChangedFrom '
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),i.' + @fieldname + ') AS ChangedTo '
SELECT @sql = @sql + ',''' + @UpdateDate + ''' AS ChangedDate '
SELECT @sql = @sql + ', COALESCE(i.LastModifiedBy, d.LastModifiedBy) AS ChangedBy'
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + ' on i.ProgramCode = d.ProgramCode'
SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS NOT NULL)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' IS NULL)'
SELECT @sql = @sql + ' FOR XML AUTO, ELEMENTS)'
SET @ParmDefinition = N'@auditBodyOUTPUT xml output'
PRINT @SQL
INSERT INTO BROKER_TEST VALUES (@auditBody, 'Before EXEC Trigger')
EXEC sp_executesql @sql, @ParmDefinition, @auditBodyOUTPUT = @auditBody OUTPUT
INSERT INTO BROKER_TEST VALUES (@auditBody, 'Trigger')
PRINT CAST(@auditBody as NVARCHAR(MAX))
EXEC BROKER_SendProgramAuditData @auditBody
END
END
END

DROP TABLE #ins
DROP TABLE #del

Can you guide me here.

Thanks and Regards,
Hemant.

Worked like a charm! Thank you for your help. I'm...

2009-04-01T12:45:00.000-05:00

Worked like a charm! Thank you for your help. I'm in the process of teaching myself SQL, and there are definitely nuances that I've yet to master.

Again thank you.

jfish222 -
Your problem is not with the dynamic...

2009-04-01T08:49:00.000-05:00

jfish222 -
Your problem is not with the dynamic SQL but with the insert syntax you are using.
If you are using insert into without specifying column names SQL server will create a new table.
In order to do what you want (adjusted to some tables I have in one of my DBs):

declare @dynamicSQLString varchar(4000), @QueryString varchar(1500)

DROP table #temp --so I can keep re-running this.

CREATE Table #temp (uid int null)

INSERT INTO #temp
(uid)
select Top(1) uid
From [Transaction]

Set @QueryString = ' [CustomerID] = 1 '

Set @dynamicSQLString = '
ALTER TABLE #temp ADD Col1 int null'
Exec(@dynamicSQLString)

Set @dynamicSQLString = '
INSERT INTO #temp
(uid, Col1)
SELECT row_number() over (ORDER BY uid ASC) as idx, uid
FROM [Transaction] Where ' + @QueryString + ' select * from #temp'

Exec(@dynamicSQLString)

select * from #temp

Hi, thanks for this post, its exactly what I'm loo...

2009-03-31T21:10:00.000-05:00

Hi, thanks for this post, its exactly what I'm looking for. Unfortunately I can't get it to work.

Not Sure What I'm doing wrong but this did not work for me. The #temp referred to in my dynamic SQL doesn't appear to be the same #temp created in the calling code.

To make things interesting, I pre-fill #temp to demonstrate the issue.

#######
start
#######

Declare @dynamicSQLString varchar(4000), @QueryString varchar(1500)

drop table #temp --so I can keep re-running this.

select Top(1) * into #temp
From Transactions

Set @QueryString = ' [Customer ID] = 2 '
Set @dynamicSQLString = 'SELECT row_number() over (ORDER BY SettlementDate ASC) as idx, * into #temp
FROM Transactions Where ' + @QueryString + ' select * from #temp'

Exec(@dynamicSQLString)

select * from #temp

#######
end
#######

Notice there are two selects, first in the dynamic code, then in the outer code.
The Dynamic code's Select returns 11k records, the second select returns 1 record.

Any ideas? Thanks.

Thanks, this was driving me crazy.

Buddy S...

2008-04-25T15:56:00.000-05:00

Thanks, this was driving me crazy.

Buddy Stein
Cheltenham High '69