SQL SERVER – Creating Temporary and Global Temporary Stored Procedures

SQL SERVER – Creating Temporary and Global Temporary Stored Procedures

Unknown   17:24

We all know that SQL Server allows us to create temporary tables but it is not as much known that SQL Server allows us to create temporary as well as global stored procedures.

Temporary Stored Procedures

Here is a simple example of Temporary Stored Procedures.

 -- create a temporary stored procedure

CREATE PROCEDURE #TempSP1
@para1 INT
AS
SELECT @para1
GO
-- execute temporary stored procedure
EXEC #TempSP1 9
GO

When you run the above stored procedure, it actually executes just like a regular stored procedure but it exists in the same session where it is created.
Global Temporary Stored Procedures

If you need to access your Temporary Stored Procedures from a different session, you can also consider Global Temporary stored procedures. For this example, you will have to create a stored procedure name by prefixing with two # signs (##) instead of one # sign.

Here is an example of the Global Temporary Stored Procedure.
-- create a temporary stored procedure
CREATE PROCEDURE ##TempSP1
@para1 INT
AS
SELECT @para1
GO
-- execute global temporary stored procedure from different query window
EXEC ##TempSP1 19
GO
Well, that’s it. I would love to know if you are using Temporary Stored Procedures or not. Please leave a comment.
Reference: Vishal Dhanani (https://sql-server-authority.blogspot.com/

Read More

Import Data from a Text or CSV file into SQL Server

Import Data from a Text or CSV file into SQL Server

Unknown   06:43

This is very useful SQL Query for import Data from a Text or CSV files into SQL Server.
You can easily achieve this thing by following below steps.

1. First of all, you need to create a particular table in which you want to import CSV file.  

USE Sample
GO
CREATE TABLE Employee
(Id INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
Designation VARCHAR(40),
BirthDate SMALLDATETIME)
GO

2. My Source Data in CSV file looks as per below and I have created it in C:\Employee (CSV).

3. Use below script to import CSV Data into SQL Employee Table.

BULK
INSERT Employee
FROM 'c:\Employee (CSV).txt'
WITH
(
FIELDTERMINATOR = ',',  --CSV field delimiter
ROWTERMINATOR = '\n' ,   --Use to shift the control to next row
TABLOCK
)
GO

After Executing above script:

Please let me know any update in above script.

Thank you.

Read More

SQL Server Shortcut keys

SQL Server Shortcut keys

Unknown   02:31

SQL Query Analyzer is a great tool to have and here is a list of all shortcut keys you can use to speed up your keyboard capabilities to use Query Analyzer.

Bookmarks: Clear all bookmarks.	CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle).	CTRL+F2
Bookmarks: Move to next bookmark.	F2
Bookmarks: Move to the previous bookmark.	SHIFT+F2
Cancel a query.	ALT+BREAK
Connections: Connect.	CTRL+O
Connections: Disconnect.	CTRL+F4
Connections: Disconnect and close child window.	CTRL+F4
Database object information.	ALT+F1
Editing: Clear the active Editor pane.	CTRL+SHIFT+ DEL
Editing: Comment out code.	CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT.	CTRL+C
Editing: Cut. You can also use SHIFT+DEL.	CTRL+X
Editing: Decrease indent.	SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane.	CTRL+DEL
Editing: Find.	CTRL+F
Editing: Go to a line number.	CTRL+G
Editing: Increase indent.	TAB
Editing: Make selection lowercase.	CTRL+SHIFT+L
Editing: Make selection uppercase.	CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT.	CTRL+V
Editing: Remove comments.	CTRL+SHIFT+R
Editing: Repeat the last search or find next.	F3
Editing: Replace.	CTRL+H
Editing: Select all.	CTRL+A
Editing: Undo.	CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility).	F5
Help for SQL Query Analyzer.	F1
Help for the selected Transact-SQL statement.	SHIFT+F1
Navigation: Switch between query and result pane.	F6
Navigation: Switch pane.	Shift+F6
Navigation: Window Selector.	CTRL+W
New Query window.	CTRL+N
Object Browser (show/hide).	F8
Object Search.	F4
Parse the query and check syntax.	CTRL+F5
Print.	CTRL+P
Results: Display results in a grid format.	CTRL+D
Results: Display results in text format.	CTRL+T
Results: Move the splitter.	CTRL+B
Results: Save results to file.	CTRL+SHIFT+F
Results: Show Results pane (toggle).	CTRL+R
Save.	CTRL+S
Templates: Insert a template.	CTRL+SHIFT+INSERT
Templates: Replace template parameters.	CTRL+SHIFT+M
Tuning: Display estimated execution plan.	CTRL+L
Tuning: Display execution plan (toggle ON/OFF).	CTRL+K
Tuning: Index Tuning Wizard.	CTRL+I
Tuning: Show client statistics	CTRL+SHIFT+S
Tuning: Show server trace.	CTRL+SHIFT+T
Use database.	CTRL+U

Read More