Home > DATA BASE, SQL Server > SQL Server – Generatore di righe di Insert (Insert Genereator)

SQL Server – Generatore di righe di Insert (Insert Genereator)

Stored Procedure:

CREATE  PROC [dbo].[usp_UtiNX_InsertGenerator]
(@tableName varchar(100))
AS
--
-- Descriz.: UTILITY NX
--           Generatore di righe di insert Dati a partira dalla tabella in Input
--
-- Autore (Refactoring): NX
-- Utilizzo: exec usp_UtiNX_InsertGenerator <nomeTabella>
--

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000)     -- for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) -- for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000)   -- data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
 begin
 print 'Table '+@tableName+' not found, processing skipped.'
 close curscol
 deallocate curscol
 return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE

--if the datatype is text or something else
if @dataType in ('text','ntext')
BEGIN
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE

--because money doesn't get converted from varchar implicitly
IF @dataType = 'money'
 BEGIN
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
 END
ELSE
IF @dataType='datetime'
 BEGIN
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
 END
ELSE
IF @dataType='image'
 BEGIN
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
 END
--presuming the data type is int,bit,numeric,decimal
ELSE
 BEGIN
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
 END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000)

-- execute select
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query

CLOSE cursCol
DEALLOCATE cursCol

RETURN
Annunci
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Effettua il login con uno di questi metodi per inviare il tuo commento:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: