Bulk capture utility
cmd>BCP"select patient_info,patient_id, patient_dft, patient_evn, modifed_date, from ADT_Patient_stage_Dim where modified_date = 2015-06-17 12:31:48.157"
queryout
B:\BCP\Export_adt_20150617v1 -SSRDEV1 -UDEVUSER -PDEVLOGIN -c
cmd>BCP ADT.dbo.ADT_Patient_stage_Dim
OUT
B:\BCP\Export_adt_20150617v2 -SSRDEV1 -UDEVUSER -PDEVLOGIN -c
cmd>BCP ADT.dbo.ADT_Patient_stage_Dim
IN
B:\BCP\Export_adt_20150617v2 -SSRDEV1 -UDEVUSER -PDEVLOGIN -c
cmd>BCP ADT.dbo.ADT_Patient_stage_dim
OUT
B:\BCP\Export_adt_20150617v3 -T -c
cmd>BCP ADT.dbo.ADT_Patient_stage_dim
IN
B:\BCP\Export_adt_20150617v3 -T -c
Explanation of options:
(-T : if the SQL Server instance uses integrated security then use this option for trusted connection, otherwise specify it with -U : loginid, P:password . )
(-c :The bcp utility uses character data for the bulk copy operation. To bulk copy data between SQL Server and other applications, such as Microsoft Excel.
When you include one of these options, you are not prompted for format information. The formatting is taken care of automatically. Each field, by default, is terminated with a tab character, and each row is terminated with a newline character which can also be changed with -t ,-r options )
(-F argument to specify the first row to be retrieved and the -L argument to specify the last row)
(-o argument to specify an output file,any output that would have been displayed to the console is now saved to the output file.)
(Now when you import the data, any IDENTITY column in the table values in the data file will be loaded into the table, rather than new values being generated by the database with a -E option)
(-h: command includes the TABLOCK hint, the database engine will hold the lock for the duration of the bulk load operation, which significantly improves performance over the default row-level locks. -h ORDER can also can be used)
(To specify the batch size, include the -b argument, along with the number of rows per batch.)
(To specify an error file, use the -e argument, with full path specified)
(To create a format file, you must specify format for the mode and nul for the data file. In addition, you must also include the -f argument, followed by the full path name of the format file)
(To create an XML format file, you run a command include the -x argument to create in XML format which is more flexible and powerful than the original non-XML format file.However, as with the non-XML format file, you can specify a different format "-n:native format" other than "-c:character format")
(The format file, can be referenced in your bcp commands by specifying the -f argument, along with the name of the format file)
Notice: XML format files do not let you have more table columns than fields in the data file. To get around this limitation you have to create an updateable view against the target table and including only those columns that have corresponding fields in the data file.)
(-N:unicode native format, -W :unicode character format)
BCP with format option
cmd>BCP ADT.dbo.ADT_Patient_stage_Dim
fomat nul -c
-f B:\BCP\Export_adt_tabformat.fmt -T
cmd>BCP ADT.dbo.ADT_Patient_stage_Dim
fomat nul -c -x
-f B:\BCP\Export_adt_tabformat.xml -T
Export_adt_tabformat.fmt , Export_adt_tabformat.xml are format files exported from table to known the structure of the import dataset to be imported into table
cmd:> BCP ADT.dbo.ADT_Patient_stage_Dim
IN
B:\BCP\Hcodition.txt
-f B:\BCP\Export_adt_tabformat.xml -T -c
cmd> BCP ADT.dbo.ADT_Patient_stage_Dim
IN
B:\BCP\Hcodition.txt
-f B:\BCP\Export_adt_tabformat.txt -T -c
In query window(from table to xml)
select @@SERVERNAME. GETDATE();
select patient_info,patient_id, patient_dft, patient_evn, modifed_date, from ADT_Patient_stage_Dim where modified_date = '2015-06-17 12:31:48.157'
for xml auto,root('ADTINFO')
GO
Select(
select patient_info,patient_id, patient_dft, patient_evn, modifed_date, from ADT_Patient_stage_Dim where modified_date = '2015-06-17 12:31:48.157'
for xml auto,root('ADTINFO')
) AS DATA,
'B:\BCP\ADTdetails' AS path
GO
For metadata information on tables
DBCC CHECKIDENT(ADT_Patient_stage_Dim,NORESEED) {explain: To know the identity details}
select * from information_schema.tables where TABLE_NAME = 'ADT_Patient_stage_dim'
select * from sys.tables;
select * from sysobjects where xtype ='U'
exec sp_columns 'ADT_Patient_stage_dim'
exec sp_help 'ADT_Patient_stage_dim'
For Change Data Capture
exec sys.sp_cdc_enable_db (TO enable cdc)
Exec sys.sp_cdc_enable_table
@ Source_Schema = N 'dbo' ,
@Source_Name = N 'ADT_Patient_stage' ,
@ Role_Name = N 'cdc_user_tt' ,
@Supports_NET_changes = 1
TABLE CTAS:
select * into ADT.dbo.ADT_Patient_stage_Dimbkp from ADT_Patient_stage_Dim
------------------------------------------------------
Which job_id is using which subsystem(execute in query window)
Use [msdb]Go
Select
Srv.srvname as ServerName,
Job.name as JobName,
Prgrun.step_id,
Prgrun.step_name as StepName,
Prgrun.command,
Job.enabled,
Prgrun.Subsystem
from dbo.Sysjobs Job
join dbo.Sysjobsteps Prgrun
on Prgrun.job_id = Job.job_id
join Master.dbo.sysservers Srv
on Srv.srvid = Job.originating_server_id
where Prgrun.subsystem = 'ssis'
order by Job.name ,step_id
