Posts

Showing posts from June, 2022

Exporting data into a text or csv file and Importing the same data into another table from text file with Bulk Copy Processing(BCP)

Image
The below BCP command will export the data(OUT) from table to a text file(.txt) and from the .txt file it will import(IN) the data to another table. Commands are give below. BCP AdventureWorks2014.Person.Person out D:\Test_Person_Peron.txt -S RAMESH\PROD -T -c -b10 -t -S  --> ServerName -T  --> Windows Authentication -b10 --> The amount of batches -t    --> Delimiter values(Eg:',', SPACE, TAB) -c    --> If you pass this this will not ask for " Enter the file storage type of field <field_name> [<default>]:" Running BCP command with SQL Query here is the command The below command will BCP out to a CSV(excel file). If you see excel it will capture all column information in single column. However it will appropriately insert the data into destination table. The below BCP command will push the data into required table from .CSV file . Here is the command .

How to capture sp_whoisactive result set into a table.

The below query will push the data into histroy for our future reference or analysis purpose, we can schedule this query or we can run wehenever we need it. /* 1) The beloq query will script out sp_whoisactive stored procedure table structure in a print format. I have create table in master database, but we can create it on other database aswell. */ DECLARE @CaptureSchema VARCHAR ( MAX ) EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%] [cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]' , @return_schema = 1 , @get_plans = 1 , @ schema = @CaptureSchema OUTPUT PRINT @CaptureSchema /* The below query will push the data into a table(creating table with the script that the above resultset) please notice @destination_table parameter below. */ EXEC sp_whoisactive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tr...