CREATE PROC BulkInsertUpdate
	@db varchar(50),
	@tbl varchar(50),
	@path varchar(200)

as

begin tran

declare @truncate varchar(250)
set @truncate = 'truncate table temp_' + @tbl
exec (@truncate)
 
declare @sql varchar(250)
set @sql = 'bulk insert ' + @db + '..[temp_' + @tbl + '] from ''' + @path + '''
	 WITH (FIELDTERMINATOR = '';'')'
PRINT @sql
exec (@sql)


declare @ind varchar(100)
declare @update varchar(5000)
declare @insert varchar (5000)
declare @tempupdate varchar (5000)
declare @intAndCount int

set @update = 'Update ' + @tbl + ' set' 
set @insert = 'INSERT INTO ' + @tbl + ' ( '
set @tempupdate = 'Update temp_' + @tbl + ' SET NEWROW = 1 '


set @intAndCount = 0 ---------------------------------------- Returns Non-PK's for SET Update
DECLARE uInd CURSOR FOR 
SELECT C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE C.COLUMN_NAME NOT IN (SELECT COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE K where K.CONSTRAINT_NAME LIKE 'PK_%' AND K.TABLE_NAME = @tbl ) 
and c.TABLE_NAME = @tbl 
ORDER BY ORDINAL_POSITION

OPEN uInd

-- Perform the first fetch.
FETCH NEXT FROM uInd
into @ind


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @update = @update + ', '
set @update = @update + ' ' + @ind + ' = t.' + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM uInd
	into @ind

END
set @update = @update + ' from ' + @tbl + ' d INNER JOIN temp_' + @tbl + ' t on '
CLOSE uInd
DEALLOCATE uInd --------------------------------------------------------  End Update Non-PK's 




set @intAndCount = 0  ------------------------------------------ Returns PK's for FROM Update
DECLARE cInd CURSOR FOR 
select column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where constraint_catalog = @db
and table_name = 'temp_' + @tbl 
and constraint_name like 'PK_%'

OPEN cInd

-- Perform the first fetch.
FETCH NEXT FROM cInd
into @ind

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @update = @update + ' and '
set @update = @update + 't.' + @ind + ' = d.' + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM cInd
	into @ind
END
set @update = @update + ' WHERE t.NEWROW = 0;'
CLOSE cInd
DEALLOCATE cInd --------------------------------------------------  End FROM Update Non-PK's 



set @intAndCount = 0 ----------------------------------------- ReturnsPK's for INSERT COLUMN 
DECLARE iInd CURSOR FOR 
select column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where constraint_catalog = @db
and table_name = 'temp_' + @tbl 
and constraint_name like 'PK_%'

OPEN iInd

-- Perform the first fetch.
FETCH NEXT FROM iInd
into @ind

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @insert = @insert + ', '
set @insert = @insert + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM iInd
	into @ind
END
CLOSE iInd
DEALLOCATE iInd -----------------------------------------------------  End INSERT COLUMN PK's 


 
DECLARE inInd CURSOR FOR -------------------------------------  Returns INSERT COLUMN NON-PK's 
SELECT C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE C.COLUMN_NAME NOT IN (SELECT COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE K where K.CONSTRAINT_NAME LIKE 'PK_%' AND K.TABLE_NAME = @tbl ) 
and c.TABLE_NAME = @tbl 
ORDER BY ORDINAL_POSITION

OPEN inInd

-- Perform the first fetch.
FETCH NEXT FROM inInd
into @ind


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @insert = @insert + ', '
set @insert = @insert + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM inInd
	into @ind

END
set @insert = @insert + ' ) Select '
CLOSE inInd
DEALLOCATE inInd ------------------------------------------------  END INSERT COLUMN NON-PK's 

set @intAndCount = 0 ----------------------------------------- ReturnsPK's for INSERT SELECT 
DECLARE iInd CURSOR FOR 
select column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where constraint_catalog = @db
and table_name = 'temp_' + @tbl 
and constraint_name like 'PK_%'

OPEN iInd

-- Perform the first fetch.
FETCH NEXT FROM iInd
into @ind

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @insert = @insert + ', '
set @insert = @insert + 't.' + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM iInd
	into @ind
END
CLOSE iInd
DEALLOCATE iInd -----------------------------------------------------  End INSERT SELECT PK's 


 
DECLARE inInd CURSOR FOR -------------------------------------  Returns INSERT SELECT NON-PK's 
SELECT C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C 
WHERE C.COLUMN_NAME NOT IN (SELECT COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE K where K.CONSTRAINT_NAME LIKE 'PK_%' AND K.TABLE_NAME = @tbl ) 
and c.TABLE_NAME = @tbl 
ORDER BY ORDINAL_POSITION

OPEN inInd

-- Perform the first fetch.
FETCH NEXT FROM inInd
into @ind


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @insert = @insert + ', '
set @insert = @insert + 't.' + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM inInd
	into @ind

END
set @insert = @insert + ' FROM temp_' + @tbl + ' t where t.NEWROW = 1'
CLOSE inInd
DEALLOCATE inInd ------------------------------------------------  END INSERT SELECT NON-PK's 






set @intAndCount = 0 --------------------------------- ReturnsPK's for tempupdate Join clause 
set @tempupdate = @tempupdate + 'from temp_' + @tbl + ' t LEFT OUTER JOIN ' + @tbl + ' d on '
DECLARE tuInd CURSOR FOR 
select column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where constraint_catalog = @db
and table_name = 'temp_' + @tbl 
and constraint_name like 'PK_%'

OPEN tuInd

-- Perform the first fetch.
FETCH NEXT FROM tuInd
into @ind

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @tempupdate = @tempupdate + ' and '
set @tempupdate = @tempupdate + 't.' + @ind + ' = d.' + @ind
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM tuInd
	into @ind
END
CLOSE tuInd
DEALLOCATE tuInd -----------------------------------------END PK's for tempupdate Join clause



set @intAndCount = 0 ------------------------------- Returns PK's for tempupdate WHERE clause 
set @tempupdate = @tempupdate + ' WHERE '
DECLARE tupInd CURSOR FOR 
select column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where constraint_catalog = @db
and table_name = 'temp_' + @tbl 
and constraint_name like 'PK_%'

OPEN tupInd

-- Perform the first fetch.
FETCH NEXT FROM tupInd
into @ind

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
if @intAndCount != 0
	set @tempupdate = @tempupdate + ' and '
set @tempupdate = @tempupdate + 'd.' + @ind + ' is null'
set @intAndCount = @intAndCount + 1
   
   FETCH NEXT FROM tupInd
	into @ind
END
CLOSE tupInd
DEALLOCATE tupInd -------------------------------------------------  Returns PK's for tempupdate WHERE clause 







if @@error = 0
begin
	print 'TEMP UPDATE STATEMENT, MARKS RECORDS IN TEMP_TBL AS INSERTS'
    print @tempupdate
	exec (@tempupdate)
	print 'UPDATE STATEMENT, UPDATES RECORDS FROM TEMP to TBL where NEWROW = 0'
	print @update
    exec(@update)
	print 'INSERT STATEMENT, INSERTS RECODRDS FROM TEMP to TBL where NEWROW = 1'
	print @insert
	exec(@insert)
	print 'success'
	commit
end
else
begin
	print 'error'
	rollback
end



GO