Useful SQL Snippets

These are handy little snippets of SQL that see frequent use, particular for non-standard maintenance tasks.

Create A Copy Of A Record

SELECT * INTO TempTable FROM your_table WHERE id ='sourceID';
Update TempTable SET id='targetID';
INSERT INTO your_table SELECT * FROM TempTable;
DROP TABLE TempTable;

Reset the Auto Increment.

Delete From credits2018
Where 1=1

DBCC CHECKIDENT ('credits2018', RESEED, 1)

Retrieve ID of New Record

By simply calling the .execute() function for the created query object, as opposed to calling .execute().getResults() function call, you'll have access to a wealth of information in the returned object, including the ID value when performing a SQL Insert. The returned object supports a .getPrefix() function that includes a generatedKey value as follows:

try{
	queryObj = new Query(
		name="qryGet", 
		datasource="FirstInterview",
		sql = 	"INSERT INTO Cars (make,model) VALUES ('Dodge','Charger'"); 
		returnedObj = queryObj.execute();
} catch (any e) {
	
}
var newRecordID =  returnedObj.getPrefix().generatedkey;

Retrieve the Executed SQL Statement

Likewise, the .getPrefix() can be used to retrieve other query related values, such as the SQL statement that was executed:

var sqlStatement = returnedObj.getPrefix().sql;

Note that the getPrefix().sql results will not include the parameters used in the SQL statement (if any). If you want the sql parameter values, you can also output getPrefix().sqlParameters or simply dump the whole getPrefix() structure.

Retrieve Schema For Table

A simple query can be used to retrieve the column names, data types, and an assortment of other information for any table in a database in Microsoft SQL Server. The following example retrieves all of the information for the "cars" table in the "Automobiles" database:

SELECT *
FROM Automobiles.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Cars'