Recently read that to bulk copy data in sql, we can use bcp command. Below is a small example which copies data from one table to another.

It initially copies data to a bcp file from the original table. Then copy it to the destination table from the file. The sql query can also be written in a view and retrieved from there.

DECLARE
      @FileName varchar(50)
    , @bcpCommandfirst varchar(2000)
    , @bcpCommandsecond varchar(2000)

SET @FileName = 'd:\bcptest\sitetest.bcp'

SET @bcpCommandfirst  = 'bcp "SELECT sitenavigationid, linktext, linkurl, ParentSiteNavigationId FROM mydb..sitenavigation" queryout "' 

SET @bcpCommandfirst  = @bcpCommandfirst + @FileName + '" -U sa -P pass -c '

SET @bcpCommandsecond = 'bcp "mydb..sitenavigationcopy3" in "'+ @FileName + '" -U sa -P pass -c '

PRINT @bcpCommandfirst
PRINT @bcpCommandsecond

EXEC master..xp_cmdshell @bcpCommandfirst
EXEC master..xp_cmdshell @bcpCommandsecond

select * from dbo.[sitenavigation]
select * from dbo.[sitenavigationcopy3]

Published under: SQL Server Tips ·  ·  ·  ·