|
|
Dynamically declare the variables for a table (to use with insert/update)
Last post 07-25-2008, 3:53 PM by twaligora. 0 replies.
-
 07-25-2008, 3:53 PM |
-
twaligora
-
-
-
Joined on 01-11-2007
-
-
Posts 35
-
-
|
Dynamically declare the variables for a table (to use with insert/update)
This will only work if your column names have no Spaces for example column PoNum is Ok, Po Num wont work
The update will need some tweaking
Declare @TableName varchar(100)
set @TableName = 'FT_ALLOWANCES_TYPE' --Pass your table name inhere
declare @TempVariables table (DecVars varchar(100))
insert into @TempVariables
SELECT
CASE WHEN DATA_TYPE = 'varchar' THEN
'@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH) + ')'
ELSE
CASE WHEN DATA_TYPE = 'numeric' or DATA_TYPE = 'decimal'
THEN
'@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR,NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR, NUMERIC_SCALE) + ') '
ELSE
'@' + COLUMN_NAME + ' ' + DATA_TYPE
END
END
FROM
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE
TABLE_NAME = @TableName
declare @ReturnStr varchar(100), @MyVariables varchar(5000)
DECLARE MyVariableCursor CURSOR FOR
select * from @TempVariables
Open MyVariableCursor
fetch next from MyVariableCursor into @ReturnStr
while @@FETCH_STATUS = 0
begin
if @MyVariables is null
begin
set @MyVariables = @ReturnStr
end
else
begin
set @MyVariables = @MyVariables + ', ' + @ReturnStr
end
fetch next from MyVariableCursor into @ReturnStr
end
Close MyVariableCursor
Deallocate MyVariableCursor
select @MyVariables as MyParameters
-------------------------------------------NOW GET THE INSERT
declare @TempInsert table ([ID] [int] IDENTITY(1,1) NOT NULL, DecInst varchar(100))
insert into @TempInsert
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE
TABLE_NAME = @TableName
declare @MyInsert varchar(5000)
DECLARE MyInsertCursor CURSOR FOR
select DecInst from @TempInsert
Open MyInsertCursor
fetch next from MyInsertCursor into @ReturnStr
while @@FETCH_STATUS = 0
begin
if @MyInsert is null
begin
set @MyInsert = 'Insert into ' + @TableName + ' ('+ @ReturnStr
end
else
begin
set @MyInsert = @MyInsert + ', ' + @ReturnStr
end
fetch next from MyInsertCursor into @ReturnStr
end
Close MyInsertCursor
Deallocate MyInsertCursor
set @MyInsert = @MyInsert + ')'
select @MyInsert as MyInsert
-------------------------------------------NOW GET THE VALUES
declare @TempValues table ([ID] [int] IDENTITY(1,1) NOT NULL, DecVals varchar(100))
insert into @TempValues
SELECT
'@' + COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE
TABLE_NAME = @TableName
declare @MyValues varchar(5000)
DECLARE MyValueCursor CURSOR FOR
select DecVals from @TempValues
Open MyValueCursor
fetch next from MyValueCursor into @ReturnStr
while @@FETCH_STATUS = 0
begin
if @MyValues is null
begin
set @MyValues = 'Values (' + @ReturnStr
end
else
begin
set @MyValues = @MyValues + ', ' + @ReturnStr
end
fetch next from MyValueCursor into @ReturnStr
end
Close MyValueCursor
Deallocate MyValueCursor
set @MyValues = @MyValues + ') '
select @MyValues as MyValues
-------------------------------------------- Lastly do the Update
delete from @TempVariables
insert into @TempVariables
select
ti .DecInst + ' = ' + tv.DecVals
from
@TempInsert ti
inner join @TempValues tv on ti.ID = tv.ID
--select * from @TempVariables
declare @MyUpdate varchar(5000)
DECLARE MyUpdateCursor CURSOR FOR
select * from @TempVariables
Open MyUpdateCursor
fetch next from MyUpdateCursor into @ReturnStr
while @@FETCH_STATUS = 0
begin
if @MyUpdate is null
begin
set @MyUpdate = 'Update ' + @TableName + ' Set ' + @ReturnStr
end
else
begin
set @MyUpdate = @MyUpdate + ', ' + @ReturnStr
end
fetch next from MyUpdateCursor into @ReturnStr
end
Close MyUpdateCursor
Deallocate MyUpdateCursor
set @MyUpdate = @MyUpdate + ' Where'
select @MyUpdate as MyUpdate
|
|
|
|