|
I only tested in SQL Server and INTO clause is not required there.
|
|
|
|
|
Hey dude great solution thanks a lot keep making this type of articles thanks a lot one again..
|
|
|
|
|
|
Very useful. Thanks Sumit.
|
|
|
|
|
|
|
|
|
Thanks Sumit.
This works great but my tables are so long it has issues.
I went to vchar and it was still not enough.
I'm having trouble trying to do text with a local variable issue.
Any thoughts.
Thanks
|
|
|
|
|
|
Almost exactly what I was looking for and saved me a heap of time.
I added a second parameter to pass an optional where clause through to constrain the output for my purposes as well, but was easy to do.
Cheers, Adrian
|
|
|
|
|
Tks a Lot for this useful script!
|
|
|
|
|
|
I had a unique situation where I had to migrate a SQL DB to a server where I did not have any access but a web SQL prompt.
The only way to migrate data was to execute the SQL insert script on the web SQL prompt.
Your script helped a lot.
-Uday
udyaa
|
|
|
|
|
Thanks very much for this slick and very efficient piece of code. It saved me hours of work and I really appreciate your talent and creativity. Great code!
Again, Thanks!!!
|
|
|
|
|
How can I get this to emulate SQLPUBWIZ?
I have five tables that I would like to script a INSERT query for.
The resulting file would contain the INSERT code for all five tables.
Perhaps a batch file that comes loaded with the 5 file names?
Which in turn generates a 2nd .sql file.
Which would then be executed on the remote Host.
My attempts to do this were frustrated by the PRINT command getting confused with special characters ( ',", etc), When I was attempting to have TSQL code written to the resulting script file.
Thanks
modified on Tuesday, May 12, 2009 7:23 AM
|
|
|
|
|
with the little changes of table_schema for the table, so i can use this script now.
------------------------------------------------------------------------------------
CREATE PROC InsertGenerator
(@tableName varchar(100)) as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type,table_schema 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
DECLARE @schemaType nvarchar(100) --table schema value for correct schema execution
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
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+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
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(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string='INSERT '+@schemaType+'.'+@tableName+'('
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@schemaType+'.'+@tableName
Print @query
exec sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
GO
|
|
|
|
|
thank you for this update!
|
|
|
|
|
Just what I was looking for to make upload data in GoDaddy easier, since they do not allow you to connect to SQL Server from a local enterprise manager.
Great work!!!
|
|
|
|
|
Oye gracias muy bueno tu aporte!! Me salvaste de un buen trabajo
Galo.
|
|
|
|
|
create PROCEDURE [dbo].[procUtils_InsertGenerator]
(
@domain_user varchar(50),
@tableName varchar(100)
)
as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
--old takes the PK's SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
/* NEW without PK's */
select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName ) and c.is_identity=0
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
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @tableName , @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+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
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(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query
CLOSE cursCol
DEALLOCATE cursCol
/*
use MyDataBase
go
DECLARE @RC int
DECLARE @domain_user varchar(50)
DECLARE @tableName varchar(100)
-- TODO: Set parameter values here.
set @domain_user='myDomainUser'
set @tableName = 'MyTableName'
EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]
@domain_user
,@tableName
*/
<div class="ForumMod">modified on Wednesday, November 19, 2008 1:41 PM</div>
|
|
|
|
|
this has been very useful to me.
|
|
|
|
|
<code>
-- MSSQL 2005
declare @table_name nvarchar(100)
declare @columns nvarchar(max)
declare @values nvarchar(max)
declare @identity bit
declare @sql nvarchar(max)
set @table_name = 'MyTable'
set @columns =''
set @values =''
set @identity = 0
select
@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns = @columns + ',' + '['+column_name+']',
@values = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] as varbinary(max))),''NULL'')'
from
information_schema.columns
where
table_name = @table_name and data_type != 'timestamp'
set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'
</code>
|
|
|
|
|
You are right, this is much better!
|
|
|
|
|
Where have you been
This is much cool
Any thing am ready
|
|
|
|
|