动态创建临时表,插入到临时表中,然后选择 [英] Dynamically create temp table, insert into temp table, and then select
问题描述
基本上,我希望能够基于现有表动态创建临时表,然后将值插入到临时表中,然后选择插入的值.
Basically i want to be able to dynamically create a temp table based off of an existing table, and then insert values into the temp table, and select the inserted values.
我可以在其中创建临时表,可以很好地工作,只是插入和选择表格效果不佳.
i've got the part where i can create the temp table working just fine, it's just that inserting and selecting form it aren't working too well.
这是我当前的代码.
declare @table table
(
OrdinalPosition int,
ColumnName nvarchar(255),
DataType nvarchar(50),
MaxChar int,
Nullable nvarchar(5)
)
declare @i int
declare @count int
declare @colname nvarchar(255), @datatype nvarchar(50), @maxchar int
declare @string nvarchar(max)
declare @tblname nvarchar(100)
set @tblname='Projects'
set @string='create table #' + @tblname + ' ('
insert into @table
(
OrdinalPosition,
ColumnName,
DataType,
MaxChar,
Nullable
)
SELECT
ORDINAL_POSITION ,
COLUMN_NAME ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tblname
set @i=1
select @count=count(*) from @table
while (@i<=@count)
begin
select @colname=ColumnName from @table where OrdinalPosition=@i
select @datatype=DataType from @table where OrdinalPosition=@i
select @maxchar=MaxChar from @table where OrdinalPosition=@i
if (@maxchar is null)
begin
set @string = @string + @colname + ' ' + @datatype
end
else
begin
set @string = @string + @colname + ' ' + @datatype + '(' + cast(@maxchar as nvarchar(20)) + ')'
end
if (@i=@count)
begin
set @string = @string + ')'
end
else
begin
set @string = @string + ', '
end
set @i=@i+1
end
select @string
exec(@string)
set @string='
insert into #Projects (pk_prID, prWASSN_ID, prProjectStatus, prBusinessUnit, prServiceLine, prStudyTypeCode, prStudyNumber, prTimePoint, prStudyDirector,
prGroupLeader, prBookedDate, prBookedAmount, prConsumed, prBudgetedHours, prFinalReport, prFinalYear, prFinalMonth, prStartQA,
prLabWorkStarted, prLabWorkCompleted, prProjImpDate, prCompanyName, prCompanyNumber, prIsFTE, prRevisedDeadlineDate, prProjectFinalized,
prBookedYear, prBookedMonth, prCRMQuoteID, prLineItemNumber, prDraftReport, prInternalTargetDeadlineDate, prProtocolSignedDate,
prDataToRWS, prRWSWorkStarted, prFirstDraftToPL, prFirstDraftToQA, prArchivedDate, prToPLForQACommentReview,
prAnticipatedProjectArchiveDate, prToQAWithPLCommentResponse, prProjectReactivatedDate, prQAFinishDate, prSecondDraftReportToClient)
select *
from cube.Projects'
select @string
exec (@string)
set @string='select * from #Projects'
exec (@string)
这是我得到的错误:
(受影响的44行)
(受影响的1行)
(受影响的1行) 消息208,级别16,状态0,第2行 无效的对象名称"#Projects". 消息208,级别16,状态0,第1行 无效的对象名称"#Projects".
(1 row(s) affected) Msg 208, Level 16, State 0, Line 2 Invalid object name '#Projects'. Msg 208, Level 16, State 0, Line 1 Invalid object name '#Projects'.
推荐答案
尝试使用两个##命名表,这将创建一个全局临时表.范围界定可能是一个问题,您可能正在使用exec创建表,但返回时看不到它.
Try to name the table with two ##, this will create a global temp table. It could be an issue with scoping, you might be creating the table with exec but it is not visible when it comes back.
这篇关于动态创建临时表,插入到临时表中,然后选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!