Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using BCP to generate INSERT scripts Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 2:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 36, Visits: 283
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.
Post #1371290
Posted Thursday, October 11, 2012 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1371383
Posted Thursday, October 11, 2012 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 36, Visits: 283
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.
Post #1371541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse