Insert Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert Problem

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-12-14 : 12:45:26

The following query configures data I have stored in a temp table to the way I would like it in a main table. But when I add the first line for the INSERT to the main table I get the error response "Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DECLARE'."

Without the INSERT line the query works. Any help would be appreciated.

GC

INSERT SystemHolidaySick
DECLARE @CostCenter CHAR(9)
SET @CostCenter ='0961/6027'
SELECT SUBSTRING(CostCenter,1,PATINDEX('%/%', CostCenter) - 1)AS'Station',
SUBSTRING(CostCenter,PATINDEX('%/%', CostCenter)+1,DATALENGTH(CostCenter))AS'Branch',
[TempData].[CompanyCode],
[TempData].[EmployeeID],
[TempData].[CostCenter],
[TempData].[PayDate],
CASE
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 20)) THEN 'DEC 20'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 21)) THEN 'DEC 21'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 22)) THEN 'DEC 22'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 23)) THEN 'DEC 23'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 24)) THEN 'DEC 24'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 25)) THEN 'DEC 25'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 26)) THEN 'DEC 26'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 27)) THEN 'DEC 27'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 28)) THEN 'DEC 28'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 29)) THEN 'DEC 29'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 30)) THEN 'DEC 30'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (DD,[PayDate])= 31)) THEN 'DEC 31'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 01)) THEN 'JAN 01'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 02)) THEN 'JAN 02'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 03)) THEN 'JAN 03'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 04)) THEN 'JAN 04'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 05)) THEN 'JAN 05'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (DD,[PayDate])= 06)) THEN 'JAN 06'
ELSE 'Test'
END AS 'DATE',
CASE
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (YY,[PayDate])= 2001)) THEN '2001'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (YY,[PayDate])= 2002)) THEN '2002'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (YY,[PayDate])= 2003)) THEN '2003'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (YY,[PayDate])= 2004)) THEN '2003'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (YY,[PayDate])= 2004)) THEN '2004'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (YY,[PayDate])= 2005)) THEN '2004'
WHEN ((DATEPART (MM,[PayDate])= 12)AND (DATEPART (YY,[PayDate])= 2005)) THEN '2005'
WHEN ((DATEPART (MM,[PayDate])= 01)AND (DATEPART (YY,[PayDate])= 2006)) THEN '2005'
ELSE 'Test'
END AS 'Year',
[TempData].[PayCode],
[TempData].[PayHours]
FROM [TempData]

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 12:54:36

Well, two answers for you Gary:

1) I don't see anywhere in your SELECT statement that you are using the @CostCenter variable that you have declared, so maybe you could just delete the DECLARE and SET statements.

2) If there is some need for that variable, then move the DECLARE and SET statements to be before the INSERT statement.

-----------------------------------------
Professional IT Solutions from Infoneering

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-12-14 : 13:10:07

AjarnMark,

Got it!!! I was putting the cart before the horse.

Many thanks and Happy Holidays.

GC

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 13:14:58

Glad to be of help, and Merry Christmas to you, too!

-----------------------------------------
Professional IT Solutions from Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources