Loading Data From Staging Table
The problem:
"... I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column.... "
The solution:
SET NOCOUNT ON
go
CREATE TABLE Target(TargetID INT PRIMARY KEY, someData CHAR(1))
INSERT Target VALUES(1, 'A')
INSERT Target VALUES(2, 'B')
go
create table staging(someData CHAR(1))
INSERT staging VALUES('C')
INSERT staging VALUES('D')
go
SELECT IDENTITY(INT, 1,1) AS TargetID, someData
INTO #t FROM staging
INSERT Target
SELECT TargetID + (SELECT COALESCE(MAX(TargetID), 0) FROM Target),
someData
FROM #t
go
SELECT * FROM Target
go
TargetID someData
----------- --------
1 A
2 B
3 C
4 D
DROP TABLE Target
DROP TABLE Staging
DROP TABLE #t
posted by Alex Kuznetsov @ 7:35 PM
1 comments
1 Comments:
Another way to solve this is to write up a sproc to populate the incoming rows during run time:-
Let us say -->
1 a
2 b
3 c
is what we want to insert:-
ALTER procedure usp_insertNumber
(
@table varchar(10), --table on which the insert should work
@checkval varchar(10),-- value on which the increment should occur
@valueins char(1) --value to be inserted
)
as
begin
declare @execSQL nvarchar(MAX)
set @execSQL = '
declare @temp varchar(5)
select @temp =convert(varchar,(ISNULL(MAX('+@checkval+'),0)+1)) from '+ @table+'
insert into '+ @table+' values (convert(int,@temp),'''+@valueins+''')'
exec sp_executesql @execSQL
PRINT @execSQL
end
ex: exec usp_insertNumber 'test_1','abcd','a'
you could loop over the source table and call the sproc to populate the table. Usually this solution is valid only when effective debugging is required. Also the table structure can keep changing so there would one sproc per table.
Post a Comment
<< Home