bulkcopy mechanism in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

bulkcopy mechanism in sql server

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-10-31 : 04:53:35

Hi,

Inorder to move data from one table(whose identity seed is true)to another similar table for a particular cut off date,I would like to know, is there any bulkcopy mechanism in Sql server?
Currently, am moving batch by batch inside a loop.
For instance, I would like to move data for about 5 -10 tables below cut off date jan 1 2010 to another db.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 05:00:56

you can use bcp with queryout option

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 06:19:50

You might find it easier to create views on the tables for the data you need and bcp those.
Copying the data to files (native format) allows you to run the imports multiple times for one export without impacting the source tables.
You could also look at ssis but that's probably complicated for what you want to do.
Another option is an insert with a bulk openrowset - again bcp out and in (perhaps bulk insert for the in) is simplest.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-11-03 : 00:38:32

Thanks visakh and nigelrivett for the guidance. The link was quite useful.
I could copy from Original table to the other
by first copying to a bcp file and then to the second table from that.

Now,am trying to figure out
a) is it possible to copy directly from first table to second table
in a single step.

b) to filter the records below a cut off date like below:but seems to be not working.
SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation where createddate < "2007-05-10" " queryout "'

-----------------------------full sample code which worked---------
DECLARE @FileName varchar(50), @bcpCommandfirst varchar(2000)
,@bcpCommandsecond varchar(2000)

SET @FileName = 'd:\pdf\bcptest\site4.bcp'

SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation" queryout "'

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

SET @bcpCommandsecond = 'bcp "mystudyr2..sitenavigationcopy2" in "'+ @FileName + '" -U sa -P pass@12 -c '

PRINT @bcpCommandfirst
PRINT @bcpCommandsecond

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

select * from mystudyr2.dbo.[sitenavigation]
select * from mystudyr2.dbo.sitenavigationcopy2

quote:
Originally posted by visakh16

you can use bcp with queryout option

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:19:22

quote:
Originally posted by razeena

Thanks visakh and nigelrivett for the guidance. The link was quite useful.
I could copy from Original table to the other
by first copying to a bcp file and then to the second table from that.

Now,am trying to figure out
a) is it possible to copy directly from first table to second table
in a single step.
possible by using insert...select
b) to filter the records below a cut off date like below:but seems to be not working.
whats the error you're getting? replace " around date value with ' and try

SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation where createddate < "2007-05-10" " queryout "'

-----------------------------full sample code which worked---------
DECLARE @FileName varchar(50), @bcpCommandfirst varchar(2000)
,@bcpCommandsecond varchar(2000)

SET @FileName = 'd:\pdf\bcptest\site4.bcp'

SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation" queryout "'

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

SET @bcpCommandsecond = 'bcp "mystudyr2..sitenavigationcopy2" in "'+ @FileName + '" -U sa -P pass@12 -c '

PRINT @bcpCommandfirst
PRINT @bcpCommandsecond

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

select * from mystudyr2.dbo.[sitenavigation]
select * from mystudyr2.dbo.sitenavigationcopy2

quote:
Originally posted by visakh16

you can use bcp with queryout option

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-03 : 04:24:49

Hi,

Please check this link also

http://msdn.microsoft.com/en-us/library/ms186335.aspx

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:27:41

the statement should look like


SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation where createddate < ''2007-05-10'' " queryout "'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-11-03 : 07:39:07

Great! It worked.. Thanks again!

Regarding my other question, I was just curious to know whether its possible to bulk copy
from the table 'sitenavigation' to 'sitenavigationcopy2' table directly using bcp?
At the moment, am executing two bcp commands. Is it possible to reduce to one?

quote:
Originally posted by visakh16

the statement should look like

SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation where createddate < ''2007-05-10'' " queryout "'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 07:43:04

quote:
Originally posted by razeena

Great! It worked.. Thanks again!

Regarding my other question, I was just curious to know whether its possible to bulk copy
from the table 'sitenavigation' to 'sitenavigationcopy2' table directly using bcp?
At the moment, am executing two bcp commands. Is it possible to reduce to one?

quote:
Originally posted by visakh16

the statement should look like

SET @bcpCommandfirst = 'bcp "SELECT * FROM mystudyr2..sitenavigation where createddate < ''2007-05-10'' " queryout "'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

no way to directly bcp between tables

you need use insert...select itself in that case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources