Create sql server stored procedure

HuuM 46 Reputation points
2021-04-05T20:42:57.6+00:00

I have a query to run as below and i want to run below as a stored procedure, how can i

SELECT *
INTO database.schema.table
FROM database.schema.table

delete from database.schem.table

please assist

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-04-05T21:07:32.527+00:00

    Here is an example of a stored procedure and how to execute it: 

    CREATE PROCEDURE MySP @par1 int,
                      @par2 nvarchar(40) AS
   SELECT  col1, col2, col3
   FROM    sometable
   WHERE   keycol1 = @par1
     AND   keycol2 = @par2
go
EXEC MySP 36, 'xyz'

    Beware that CREATE PROCEDURE must be a batch of its own. There can be no statements directly before CREATE PROCEDURE, but there must be a go separator first. And you also need a go at the end of the procedure to delimit from statements that follows.

    (Keep in mind that go is not an SQL statement. That's an instruction to the query tool to split up the script in batches and send one batch at a time to SQL Server.)

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-04-06T01:53:20.157+00:00

    Hi @KShahzad-8722，

    Welcome to microsoft TSQL Q&A forum!

    Please try: 

        create procedure a_stu  
    as  
    SELECT *  
    INTO database.schema.table  
    FROM database.schema.table  
      
    --delete from database.schem.table  
      
    execute a_stu

    The execute a_stu statement must be executed separately from other statements.

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.