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.
here's my current code.
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)
this is the error that i get:
(44 row(s) affected)
(1 row(s) affected)
(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'.