Generazione statement di insert per i record di una tabella con SQL Server 2005

Home | SQL Server

E' capitato di dover importare i record di ua tabella e di dover usare per forza le query di insert. Ecco una query (e non una stored che magari non possiamo creare sul DB per via di mancate grant).

declare @tableName nvarchar(100)
set @tablename = 'QuiNomeDellaMiaTabella'
declare @whereClause nvarchar(MAX) 
set @whereClause = 'QuiEventualeClausolawhere'
declare @orderByClause nvarchar(MAX) 
set @orderByClause = 'QuiCampiOrdinamento'

DECLARE @tableHasIdentity bit
DECLARE @sql nvarchar(MAX)
DECLARE @cols nvarchar(MAX)
DECLARE @vals nvarchar(MAX)
SET @cols = ''
SET @vals = ''

SELECT @tableHasIdentity = 
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableName

IF @tableHasIdentity = 1
	BEGIN
	SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
		@tableName + ' ON '' FROM ' + @tableName
	EXEC sp_executesql @sql
	END

SELECT @cols = @cols + ',' + '[' + column_name + ']', @vals = @vals + 
	'+'',''+ISNULL(master.dbo.fn_varbintohexstr(cast([' + 
	column_name + '] as varbinary(max))),''NULL'')' 
FROM INFORMATION_SCHEMA.columns 
WHERE TABLE_NAME = @tableName and DATA_TYPE != 'timestamp'

SET @sql = 'SELECT ''INSERT INTO [' + @tableName + '] (' + 
	SUBSTRING(@cols,2,LEN(@cols)) + ') ' + 
	'VALUES (''+' + SUBSTRING(@vals, 6, 
	LEN(@vals)) + '+'')'' FROM ' + @tableName

IF LEN(@whereClause) > 0
	SET @sql = @sql + ' WHERE ' + @whereClause
	IF LEN(@orderByClause) > 0
	SET @sql= @sql + ' ORDER BY ' + @orderByClause

exec sp_executesql @sql

IF @tableHasIdentity = 1
	BEGIN
	SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
		@tableName + ' OFF '' FROM ' + @tableName
	EXEC sp_executesql @sql
	END
Home