Do you like to create an insert into from tables ?
-
In the past I found a script (store proc) that create an insert into command with all fields from a table.
I gave a table name to the procedure an the
result was this =>
INSERT INTO mytab VALUES (1,"R000001","PPROJEKT VODAFONE",null,null,null,null)
Not bad but my SQLServer7 need also the name
of fields and not only the data information.
Here is a new version.
CREATE PROCEDURE Data_ins_Replic(@Tabelle char(50) ) AS
DECLARE @TableList varchar(4000)
DECLARE XCursor CURSOR FOR /* Cursor*/
select left(column_name, 50) as 'Field ' from information_schema.columns
where table_name = (RTrim(@Tabelle))
OPEN XCursor
DECLARE @Field varchar(4000);
DECLARE @Counter int;
DECLARE @Anzahl int;
DECLARE @Ausgabe varchar(4000);
SET @Counter = 0;
Set @TableList = @Tabelle
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (4000), @TableName varchar (50)
DECLARE @name varchar(256), @xtype int, @status tinyint, @IsIdentity tinyint
SELECT @TableList = @TableList + ','
SELECT @IsIdentity = 0
SELECT @position = PATINDEX('%,%', @TableList)
-----------------------
--This is new
-----------------------
Select @Anzahl = count(*) from information_schema.columns where table_name = (RTrim(@Tabelle))
FETCH NEXT FROM XCursor INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
set @counter = @counter + 1;
if @counter = 1 set @Ausgabe = '(' + @Field + ',' ; -- 1. Druchlauf
if @counter > 1 and @counter < @Anzahl set @Ausgabe = @Ausgabe + @Field + ','
if @counter = @Anzahl set @Ausgabe = @Ausgabe + @Field + ')'
FETCH NEXT FROM XCursor INTO @Field
END
CLOSE xCursor
DEALLOCATE xCursor
--------------------
WHILE (@position <> 0)
BEGIN
SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList),'')
SELECT @position = PATINDEX('%,%', @TableList)
SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR ' + 'SELECT a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @TableName + ''''
EXEC (@exec_str)
OPEN fetch_cursor
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END
SELECT @exec_str = "SELECT ' INSERT INTO " + RTRIM(@TableName)+ RTRIM( @Ausgabe) + " VALUES (' + "
Select ' -- The table name is: ' + @TableName
--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + '''"None yet"'''
ELSE
--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
ELSE
--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + 'Coalesce(' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
ELSE
--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
SELECT @exec_str = @exec_str + 'Coalesce(' + '''"'' + ' + @name + ' + ''"''' + ',"null")'
ELSE
--uniqueidentifierxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"''' + ',"null")'
ELSE
--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
ELSE
SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' + @name + '), "null")'
WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@@FETCH_STATUS = -1) BREAK
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END
--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + ' + ","' + ' + ''"None yet"'''
ELSE
--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0xFFFFFFFF' + '"'
ELSE
--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
ELSE
--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + @name + ' + ''"''' + ',"null")'
ELSE
--uniqueidentifier
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"''' + ',"null")'
ELSE
--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' + '"'
ELSE
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(CONVERT(varchar,' + @name + '), "null")'
END
----------
CLOSE fetch_cursor
DEALLOCATE fetch_cursor
SELECT @exec_str = @exec_str + '+ ")" ' + ' FROM ' + RTRIM(@TableName)
EXEC(@exec_str)
-- print (@exec_str) --- Here you can see the sql - result
SELECT 'GO'
IF @IsIdentity = 1
BEGIN
SELECT @IsIdentity = 0
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
SELECT 'GO'
END
END
OM
OM
OM -
Please post in the script library, thats where we keep the others and where readers mostly likely to look when they need code. Thanks for posting!
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply