Appending Stored Procedure

I need to make sure I'm doing this correctly can you help me out guys please?? This is an Appending Stored procedure it should move values from the EmployeeGamingLicense table when the status is turned into TERMINATED to the GCEmployeeTerms table. Heres what I have so far, having problems with the rest of the script getting errors

CREATE PROCEDURE [insert_GCEmployeeTerms_1]
(@.TM_#_1 [int],
@.FirstName_2 [nvarchar](50),
@.LastName_3 [nvarchar](50),
@.SocialSecurityNumber_4 [int],
@.DateHired_5 [datetime],
@.Status_6 [nvarchar](50),
@.TerminationDate_7 [datetime],
@.Title_8 [nvarchar](50),
@.DepartmentName_9 [nvarchar](50),
@.TermReason_10 [ntext],
@.VoluntaryInvoluntary_11 [nvarchar](50))

AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]
( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[TerminationDate],
[Title],
[DepartmentName],
[TermReason],
[VoluntaryInvoluntary])

SELECT
( @.TM_#,
@.FirstName,
@.LastName,
@.SocialSecurityNumber,
@.DateHired,
@.Status,
@.TerminationDate,
@.Title,
@.DepartmentName,
@.TermReason,
@.VoluntaryInvoluntary)
FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATED'
GOMethinks thou wants not both tables and variables in yon SELECT list. There be dragons in that mix!

-PatP|||Thank You pat for that very old English/shakesperan/King Arthur answer..lol. Let me see if I interpreted that correctly...Your saying that my select list is incorrect, refering of both tables...I need to go look it up..sorry this is my first append stored procedure.|||If you stop and think about it, you are passing a whole flock (that would be a really technical term, eh?) of parameters to the procedure. This would be a good thing if you wanted to insert those parameters one time. You are also using a table with a where clause, which would be a good thing if you wanted to insert columns from that table for every row that qualified. However, doing both means that you want to insert one copy of the parameters that you are passing for every row that qualifies... Which is probably NOT what you intended!

You probably want to either use the table with column names, or the parameters without the table, but only one or the other!

-PatP|||I'm just being nosy now, but did you ever get this one sorted out to your satisfaction?

-PatP|||Sniff Sniff Sniff ...No :(

I just want to insert data from one table to another automatically through stored procedures. I can make part of the procedure (that Inserts), need to tell it to pull from another table...I dont know, keep getting errors its like a puzzle I can only get part of it but not all (Banging head against wall)|||a whole flock of parameters

Pat. I thought it was a bevy of parameters. Hmm. May need to add it to the lists...

http://encyclopedia.thefreedictionary.com/List%20of%20collective%20nouns%20by%20collective%2 0term%20A-K|||ok so your saying its just a bunch of parameters..a horde of parameters|||Sniff Sniff Sniff ...No :(Now, now M'Dear! You musn't get too upset. Might I suggest trying something like:CREATE PROCEDURE [insert_GCEmployeeTerms_1]
AS

INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] (
[TM #], [FirstName], [LastName]
, [SocialSecurityNumber], [DateHired], [Status]
, [TerminationDate], [Title], [DepartmentName]
, [TermReason], [VoluntaryInvoluntary]
) SELECT
a.TM_#, a.FirstName, a.LastName
, a.SocialSecurityNumber, a.DateHired, a.Status
, a.TerminationDate, a.Title, a.DepartmentName
, a.TermReason, a.VoluntaryInvoluntary)
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'

RETURN
GO-PatP|||I did what you said pat I got some Errors but what you put up gave me a great start I was not Referring to the Table.Fields that will do the inserting for me Fields in that Table are not exactly the same..(the column names) OK I GET IT A LIGHT HAS GONE OFF AND I TOTALLY GET IT NOW..Now I see why SQL was like WHAT THE HELL ARE YOU TALKING ABOUT..OMG I Die a Happy Woman now...Thank you so much Pat Your Truly a SQL GOD..I totally Get it now. :)

THANK YOU|||and Mccowley was correct I see his point now|||Now that you grok (http://searchsmallbizit.techtarget.com/sDefinition/0,,sid44_gci212216,00.html) how this works, you'll need to think a bit about how to limit the carnage. You won't always want to turn that procedure loose to copy every terminated employee row, or you'll end up with a gazillion copies of each row in GCEmployeeTerms (one for every time the procedure is run while that terminated row is present), which probably isn't what you want!

-PatP|||Ok...what happens is our dept gets a list of terminated employees, so when we get this list we go to the [GamingCommissiondb].[dbo].[EmployeeGamingLicense] which is the active table look up the employee and change their status to 'TERMINATED'. Now there can be as much as 20-30 terminations. The Stored Procedure will then take those employees that have been changed to Terminated and APPEND (Insert) them into the TERMINATION.tbl. One of the purposes of doing this is if the employee comes back as a rehire we can check to see if there was any negative action taken against their gaming license. Does that make sense??|||Oh heavens yes, but unless you delete the row from the active table when this happens, that row will be copied every time the procedure runs (at least as it is written right now). The only criteria that you are using to select rows is if they are flagged as terminated... You might want to be a wee bit more selective than that, maybe throwing in a test on the termination date or something wild and crazy like that!

-PatP|||I understand what your saying, I dont want my users to delete a record in error so I didnt want to delet the record from the active table through the stored procedure. I have a view that shows only the terminated employees from the active table that I look at everyone 2 weeks, just a QC thing. also both the tables have the TM# as a primary key, the first time I tried to execute the stored procedure I got "Pk constraint error duplicates"..something like that, so once I found out what records had already been appended to the TERMINATION table and which ones had not been the SP executed successfully. Does that make sense?|||Yep, that it does. You could also use a WHERE NOT EXISTS clause to test for the presence of the TM# while you were doing the INSERT operation. This might be simpler.

-PatP|||Yes your absolutely right, I'll alter the procedure then thank you again for your help Pat I sure do appreciate it :)