October 11, 2012 at 2:40 am
Hello,
For some reason I need to generate "Records Insert" for each table of one database.
I already have a Stored Procedure that generate these inserts. Here's the code:
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
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+','''')+'''''',''+'
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'')+''''''),''+'
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'')+'''''',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000) -- provide for the whole query,
-- you may increase the size
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query --load and run the built query
CLOSE cursCol
DEALLOCATE cursCol
To be honest it works really great. I does what I expect.
EXEC mydb.dbo.InsertGenerator mytable
Outputs
INSERT mytable(col1,col2,col3,...) VALUES(val1,val2,val3,...)
Well, as I said, I need one file per table containing 'insert' statement for each record of the table.
I was thinking of using BCP to generate these files but it doesn't seems to like the way I use it :/
This is what I tried:
bcp "EXEC [mydb].dbo.InsertGenerator mytable" queryout "D:\DATA\myexportfile.sql" -T -c -Sserver\instance
I get this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]BCP host-files must contain at
least one column
I guess it's kinda normal but why ? Can't we use sp's within bcp ?
Thanks in advance
Have a nice day.
Franck.
October 11, 2012 at 5:37 am
If you check BOL for BCP (see here), you'll see that it can take a table, a view or a query as an argument. But not a proc.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2012 at 8:32 am
I finally got what I wanted but not by using BCP, but SQLCMD.
For each tables containing at least one record in my table, it creates a file with "inserts"
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
DECLARE @databasename NVARCHAR(200)=N'dbname'
DECLARE @servername NVARCHAR(50)=N'host\instance'
DECLARE @location NVARCHAR(1000)=N'D:\path_to_folder\'
DECLARE @filename NVARCHAR(200)
DECLARE @strTablename NVARCHAR(50)
DECLARE @variable VARCHAR(1000)
DECLARE MyCursor CURSOR FOR
SELECT b.name
FROM dbname.sys.partitions a
join dbname.sys.tables b
ON a.object_id=b.object_id
WHERE b.type='u'
and a.rows > 0
OPEN MyCursor
FETCH Next FROM MyCursor INTO @strTablename
WHILE @@Fetch_Status = 0
BEGIN
SET NOCOUNT ON
SET @filename = @location+@strTablename+'.sql'
SET @variable = 'sqlcmd -S '+@servername+' -E -d'+@databasename+' -Q "select ''SET IDENTITY_INSERT '+@strTablename+' ON''" >> '+@filename+''
EXEC xp_cmdshell @variable
SET @variable = ''
SET @variable = 'sqlcmd -S '+@servername+' -E -d'+@databasename+' -Q "exec '+@databasename+'.dbo.InsertGenerator '+@strTablename+'" >> '+@filename+''
EXEC xp_cmdshell @variable
SET @variable = ''
SET @variable = 'sqlcmd -S '+@servername+' -E -d'+@databasename+' -Q "select ''SET IDENTITY_INSERT '+@strTablename+' OFF''" >> '+@filename+''
EXEC xp_cmdshell @variable
SET @variable = ''
FETCH Next FROM MyCursor INTO @strTablename
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
Franck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply