SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using BCP to generate INSERT scripts


Using BCP to generate INSERT scripts

Author
Message
franck.maton
franck.maton
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 344
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.
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49843 Visits: 21151
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
franck.maton
franck.maton
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 344
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search