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: 2 days ago @ 4:43 AM
Points: 36, Visits: 293
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: Today @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: 2 days ago @ 4:43 AM
Points: 36, Visits: 293
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