CODEWITHSUNDEEP
sqlsql-serversql-server-2005
BornInChicago
BornInChicago

Reputation: 169

Odd Stored Procedure error - syntax?

I am trying to create a simple stored procedure in SQL Server 2005, and am puzzled by a syntax error I am getting.

Both tables have identical structure. tbl_Users_non_61 is empty and ready to receive the data.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Make_WEEKLY_SUMMARY 
AS
BEGIN
   SET NOCOUNT ON;
   Select (tbl_Users.UserID
      , tbl_Users.RegistrationType
      , tbl_Users.Datecompleted)
   INTO tbl_Users_non_61  
   FROM 
   SELECT tbl_Users.UserID
      , tbl_Users.RegistrationType
      , tbl_Users.Datecompleted
   FROM tbl_Users;
END
GO

Resulting error:

Msg 102, Level 15, State 1, Procedure Make_WEEKLY_SUMMARY, Line 5
Incorrect syntax near ','.**

Upvotes: 0

Views: 80

Answers (2)

Adam Wenger
Adam Wenger

Reputation: 17560

Since you just want to insert records into one table from another, there is a cleaner syntax:

INSERT INTO tbl_Users_non_61(UserId, RegistrationType, DateCompleted)
SELECT u.UserID, u.RegistrationType, u.Datecompleted
FROM tbl_Users AS u

To answer your question though, you do not need the ( ) around your first select, but you DO need them around your sub select, then you need to alias the sub-query you are referencing with the FROM:

Select t.UserID, t.RegistrationType, t.Datecompleted
INTO tbl_Users_non_61
FROM 
(
   SELECT u.UserID, u.RegistrationType, u.Datecompleted
   FROM tbl_Users AS u
) as t;

Upvotes: 3

Nonym
Nonym

Reputation: 6299

Try this on:

SELECT
    tbl_Users.UserID
  , tbl_Users.RegistrationType
  , tbl_Users.Datecompleted 
INTO tbl_Users_non_61  
FROM tbl_Users;

When you SELECT ... INTO, if the columns come from one table only, there's no need to SELECT those same columns again. Even if you had to take data from multiple columns, you still wouldn't need to reselect them, and perform a join instead.

Upvotes: 1

Related Questions