Category Archives: Programming

Generate insert scripts from a result set of SQLserver table

Requirement:
We have a table called employee which contains two columns eno, esal. The table contains ten records.
We are supposed to create a similar table with the same values in other server/database. We can create the same table by using the same CREATE table definition. Using the below method we can generate insert scripts which we can run in the destination, to add data rows. This method uses the concept of cursors.
Method:
Employee table has the following definition:

CREATE TABLE [dbo].[employee](
[eno] [int] NULL,
[esal] [int] NULL
)

Both the employee number, and salary are integers.
Below steps need to be executed in the source server/Database:

  1. Click on Results to File icon in the SQL Editor menu in Management Studio. This can be used to generate all the insert scripts in a text file. Shortcut to do this is (Ctrl+ Shift+ F).
  2. Run the below cursor query:


DECLARE @eno INT,@esal INT
DECLARE insertquery CURSOR FOR
SELECT eno,esal FROM empsalary
OPEN insertquery
FETCH NEXT FROM insertquery INTO @eno,@esal
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'insert INTO empsalary values('+CONVERT(VARCHAR,@eno)+','+CONVERT(VARCHAR,@esal)+')'
FETCH NEXT FROM insertquery INTO @eno,@esal
END
CLOSE insertquery
DEALLOCATE insertquery

  1. Running the above query will open file explorer. Select all files and save the file as insert query.txt.
  2. Use that file to add records in destination server.
Advertisements

Check if a particular text is present in multiple files using batch command file.

Requirement:
Sometimes we need to check if a particular text is present in one or many files. Let us say for example, we have text files containing employee details for different years. Now we have to check if a particular employee number (say 1234) is present in each file or not. This is a time-consuming process if done manually, and more so if we have many text files with us.
We can do this very easily using the below batch command method.
Steps:
1. Copy all the files to be searched into one folder.
2. In the same folder create a batch command file searchtext.cmd (file with .cmd extension) using notepad.
3. Copy the below batch commands into the searchtext.cmd file and save.
@echo off
SET RESULT_FILE="result.txt"
SET /p "var1=Enter the String to Search: "
pushd %~p0
type NUL > %RESULT_FILE%.tmp
FOR /f "delims=" %%a in ('dir /B /S *.txt') do (
FOR /f "tokens=3 delims=:" %%c in ('find /i /c "%var1%" "%%a"') do (
FOR /f "tokens=*" %%f in ('find /i "%var1%" "%%a"') do if %%c neq 0 echo %%f
)
) >> "%RESULT_FILE%".tmp
move %RESULT_FILE%.tmp %RESULT_FILE% >nul 2>&1
:: Open the file
"%RESULT_FILE%"
popd

4. Run the searchtext.cmd file (Double click).
5. It opens a command prompt requesting you to enter the search string. Enter the string that you want to search and press Enter.
6. It will process, and gives the search results in a new text file result.txt.
Note:
Above code can be used for text files (.txt). If we need to search for a text in a file with any other extension say xml file (.xml), we need to change the extension in the first for loop.
FOR /f “delims=” %%a in (‘dir /B /S *.xml’) do (