Below there is a typical DSQLT-Template.

The part above BEGIN could be used as a normal procedure.
Usually you create some input for the template. In this sample we need a column-list for the given table.
At last you call DSQLT.Execute to execute the template.

In this sample, the template-parameter @1 will be replaced with the content of @Schema.
The content of @Table will replace the parameter @2 and @3 is replaced with the column-list of the table.
Parameter @4 is replaced with the content of @Database.

CREATE PROCEDURE [Sample].[@CopyTableContentFrom]
  @Schema [sysname]
, @Table [sysname]
, @Database [sysname]=null
, @Print INT=0
AS
if @Database is null 
	SET @Database=DB_NAME()

Declare @3 nvarchar(max)
set @3= DSQLT.ColumnList(@Schema+'.'+@Table)

exec DSQLT.[Execute] '[Sample].[@CopyTableContentFrom]'
         ,@Schema,@Table,@3,@Database,@Print=@Print
RETURN  -- here ends normal SQL-Code. The rest between BEGIN and END is the Template

BEGIN
-- @0 = Current-Database  (always)
-- @1 = Schemaname
-- @2 = Tablename
-- @3 = column-list
-- @4 = Database 
-- check, if there is an identity-field in the target-table.  
IF IDENT_SEED('[@1].[@2]') is not null
	SET IDENTITY_INSERT [@1].[@2] ON

-- truncate (or delete) the target-table
BEGIN TRY
	truncate table [@1].[@2] 
END TRY
BEGIN CATCH 
	delete from [@1].[@2]  
END CATCH

-- insert data from source database (and hope there is an identical table)

insert into [@1].[@2] ("@3")
	select "@3" from [@4].[@1].[@2]
	
IF IDENT_SEED('[@1].[@2]') is not null
	SET IDENTITY_INSERT [@1].[@2] OFF

END

Last edited Apr 23, 2012 at 7:57 PM by HenrikBauer, version 6

Comments

No comments yet.