This is a super simple SQL super power trick I learned from a mentor.
If you’re working in SQL Server Management Studio (SSMS) you can open up a New Query window and type in a simple “SELECT INTO” command to backup a database table on the fly.
Sample SQL SELECT INTO Statement
SELECT * INTO new_table FROM old_table_20221122
This is pretty simple. You’re making copying of a table like a “Save As” button would let you do.
This is also great for undoing something bad you’ve done by simply reversing the process.
I always write a blog article on things that took me a little to time to find when I working on various projects.
I’m working a project with another developer and they had sent me a screenshot of a SQL stored procedure that I needed to look at but they didn’t tell me the name of the file.
I needed to take some text from the screenshot and figure out which stored procedure it was in out of 100 or more.
Below is an example of the SQL script I used to find the text I was looking for. You’ll be searching the definition for you specific text.
SQL Script Example: Find Text in Stored Procedures
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc,
m.definition
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%@RowString%';
Backward Engineering a File Parsing MicroORM Solution
I love my work!
I’m given application code I’ve never seen and in a language I don’t code in often enough and asked to figure it out.
Luckily, I have access to business analysts and a DBAs to get details about the business processes and databases the code is related to.
From code and info resources, create working test environment and get familiar enough with application code and databases to have new updates in to production in 2-3 weeks.
No pressure, right?
I was fairly successful today and here are some of the resources that used to solve problems I encountered.
Massive MicroORM
It’s a small MicroORM based on the Expando or dynamic type and allows you to work with your database with almost no effort. The design is based on the idea that the code provided to you in this repository is a start: you get up and running in no-time and from there edit and alter it as you see fit.
Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.
SQL Sample Code
DECLARE @myEmpID int
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT EmployeeID FROM Employees WHERE Company=64
OPEN myCursor
--Read the initial emploee id value from the cursor
FETCH NEXT FROM myCursor
INTO @myEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
--Update goes here
UPDATE Employees
SET [Status] = 'T'
WHERE EmployeeID = @myEmpID AND Company=54
FETCH NEXT FROM MyCursor
INTO @myEmpID
END
CLOSE MyCursor
Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.
Here is the code:
DECLARE @myEmpID int
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT EmployeeID FROM Employees WHERE Company=64
OPEN myCursor
--Read the initial emploee id value from the cursor
FETCH NEXT FROM myCursor
INTO @myEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
--Update goes here
UPDATE Employees
SET [Status] = 'T'
WHERE EmployeeID = @myEmpID AND Company=54
FETCH NEXT FROM MyCursor
INTO @myEmpID
END
CLOSE MyCursor
