Transaction and identity seed - 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 2005 Forums

Transact-SQL (2005)

Transaction and identity seed

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-05 : 15:08:15

I want to create two tables in the same stored procedure. I want to take the AlldiariesID after it's established in the first table and have it go into the second table. I've tried this but getting the following errors:

Incorrect syntax near '@dte'.
Must declare the scalar variable "@AllDiaries".
Incorrect syntax near the keyword 'convert'.

Here's my code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[AddSSIDiariesInfo]

@hun varchar (9),
@recno varchar(2),
@curstat varchar(3),
@week_start_date nvarchar(30),
@drycde varchar(2),
@drydat varchar (8),
@dryfu varchar (1),
@area varchar (3),
@dist varchar (3),
@doc varchar (3),
@pan varchar (9),
@fname varchar (15),
@lname varchar (20)

AS
set NOCOUNT on
declare @alldiariesid int
declare @dte datetime
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert into AllDiaries(hun, recno, curstat, convert(nvarchar,@dte,101) as week_start_date, drycde, drydat, dryfu)

values(@hun,
@recno,
@curstat,
@week_start_date,
@drycde,
@drydat,
@dryfu)

set @AllDiaries = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

insert into WeeklyDiariesFiles(alldiariesid,area, dist, doc, pan, convert(nvarchar,@dte,101) as week_start_date, fname, lname)

values
(@AllDiariesid,
@area,
@dist,
@doc,
@pan,
@week_start_date,
@fname,
@lname)

select @Alldiariesid as AllDiariesid --return to caller for email

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 15:14:36

Here's part of your problem: declare @alldiariesid int

Notice how you've declare a different variable than what you are using: set @AllDiaries = SCOPE_IDENTITY()

For your convert error, you can't put a function in the column list of the INSERT statement. Remove that junk and just put week_start_date. If you need to convert, then do that in the VALUES portion.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-05 : 15:40:25

Wow that was easy enough thanks!

I have another question. I might have duplicates pan's in the AllDiaries Table. How can I put an if else clause in the second query so I can have only records with the pan's and that alldiariesid to go into the WeeklyDiariesFile Table? I hope that makes sense:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[AddSSIDiariesInfo]

@hun varchar (9),
@recno varchar(2),
@curstat varchar(3),
@week_start_date nvarchar(30),
@drycde varchar(2),
@drydat varchar (8),
@dryfu varchar (1),
@area varchar (3),
@dist varchar (3),
@doc varchar (3),
@pan varchar (9),
@fname varchar (15),
@lname varchar (20)

AS
set NOCOUNT on
declare @alldiariesid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert into AllDiaries(hun, recno, curstat, week_start_date, drycde, drydat, dryfu)

values(@hun,
@recno,
@curstat,
@week_start_date,
@drycde,
@drydat,
@dryfu)

set @AllDiariesid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

insert into WeeklyDiariesFiles(alldiariesid,area, dist, doc, pan, week_start_date, fname, lname)

values
(@AllDiariesid,
@area,
@dist,
@doc,
@pan,
@week_start_date,
@fname,
@lname)

select @Alldiariesid as AllDiariesid

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 17:14:26

I don't understand your question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-05 : 21:38:45

In the AllDiaries Table the field PAN can have some duplicates

Here's some data from the AllDiaries Table:


AllDiariesID      Doc    week_start_date    PAN    Diarycode
  1               219        5/1/2009       12345     6K
  2               219        5/1/2009       12345     2L
  3               219        5/1/2009       12345     5W
  4               220        5/1/2009       12444     5W

I want This to appear in the WeeklyDiariesFile Table:



AllDiariesID      Doc    week_start_date    PAN    DiaryCode
  1               219        5/1/2009       12345      6k
  4               220        5/1/2009       12444      5w

Only two records will go in the WeeklyDiariesFile Table although there are four records in the AllDiaries Table.

I would like to use the same AllDiariesID for both tables is that possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 22:10:17

You can use the ROW_NUMBER() function to achieve this. I don't have an example with me right now to show you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 08:24:20

Okay thanks I will look that up.

One last thing you said if I want to convert the week_start_date to do it in the values section. How do I do that?


declare @dte datetime
set @dte=offices.dbo.endofweek(getdate()- 7)


DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert into AllDiaries(hun, recno, curstat, week_start_date, drycde, drydat, dryfu)

How do I put this in here?  convert(nvarchar,@dte,101) as week_start_date?

values(@hun,
@recno,
@curstat,
@week_start_date,
@drycde,
@drydat,
@dryfu)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-06 : 08:44:02

1 To delete duplicate using Row_number() function refer point 6
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

2

declare @dte datetime
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert into AllDiaries(hun, recno, curstat, week_start_date, drycde, drydat, dryfu)
values(@hun,
@recno,
@curstat,
convert(nvarchar,@dte,101),
@drycde,
@drydat,
@dryfu)

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 09:01:37

THANKS!!!! Trying Row_Number Function now.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 10:51:58

Back again with another problem. This time I created the two tables and want the info to go into the table.

Here's the two table structures:

WeeklyDiariesFiles Table
[WeeklyID] [int] NOT NULL,
[area] [varchar](3) NULL,
[dist] [varchar](3) NULL,
[doc] [varchar](3) NULL,
[pan] [varchar](9) NULL,
[fname] [varchar](15) NULL,
[lname] [varchar](20) NULL,
[week_start_date] [nvarchar](30) NULL,
CONSTRAINT [PK_WeeklyDiariesFile_2] PRIMARY KEY CLUSTERED

Here's the AllDiaries Table

[AlldiariesID] [int] IDENTITY(1,1) NOT NULL,
[WeeklyID] [int] NULL,
[hun] [varchar](9) NOT NULL,
[recno] [varchar](2) NOT NULL,
[week_start_date] [datetime] NULL,
[drycde] [varchar](2) NOT NULL,
[drydat] [datetime] NOT NULL,
[dryfu] [varchar](1) NOT NULL,
[curstat] [varchar](3) NULL

This is what I changed in the stored procedure but getting these errors:

Incorrect syntax near the keyword 'set'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Must declare the scalar variable "@Alldiariesid".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[AddSSIDiariesInfo]

set NOCOUNT on
declare @weeklyid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert WeeklyDiariesFiles
select (hun, recno, curstat, week_start_date, drycde, drydat, dryfu)

from SSIDiaries

values(@weeklyid,
@hun,
@recno,
@curstat,
convert(nvarchar,@dte,101),
@drycde,
@drydat,
@dryfu)

set @weeklyid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

select (weeklyid, area, dist, doc, pan, week_start_date, fname, lname)
into WeeklyDiariesFiles

values
(@alldiariesid,
@WeeklyId,
@area,
@dist,
@doc,
@pan,
@fname,
@lname,
convert(nvarchar,@dte,101))

From AllDiaries

select @Alldiariesid as AllDiariesid --return to caller for email

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 11:00:46

Did some tweaking and got rid of two errors

I am still getting incorrect syntax near ','

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[AddSSIDiariesInfo]

As

set NOCOUNT on
declare @weeklyid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert WeeklyDiariesFiles
select (hun, recno, curstat, week_start_date, drycde, drydat, dryfu)

from SSIDiaries

values(@weeklyid,
@hun,
@recno,
@curstat,
convert(nvarchar,@dte,101),
@drycde,
@drydat,
@dryfu)

set @weeklyid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

select (weeklyid, area, dist, doc, pan, week_start_date, fname, lname)
into WeeklyDiariesFiles

values
(@alldiariesid,
@WeeklyId,
@area,
@dist,
@doc,
@pan,
@fname,
@lname,
convert(nvarchar,@dte,101))

From AllDiaries

--select @Alldiariesid as AllDiariesid --return to caller for email

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 11:57:53

Tried this now getting incorrect syntax near the keyword 'values'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[AddSSIDiariesInfo]

As

set NOCOUNT on
declare @weeklyid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert WeeklyDiariesFiles
select weeklyid, area, dist, doc, pan, fname, lname, week_start_date

from SSIDiaries
values
(@WeeklyId,
@area,
@dist,
@doc,
@pan,
@fname,
@lname,
convert(nvarchar,@dte,101))

from SSIDiaries

set @weeklyid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

insert into AllDiaries
select weeklyid, hun, recno, week_start_date, drycde, drydat, dryfu, curstat

From WeeklyID

values
(@weeklyid,
@hun,
@recno,
convert(nvarchar,@dte,101),
@drycde,
@drydte,
@dryfu,
@curstat)

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-06 : 12:43:44

I don't understand your insert commands. They are not valid. You can't mix INSERT/SELECT with INSERT/VALUES.

Here are some valid examples:

INSERT INTO Table1 (Column1, Column2)
VALUES(@var1, @var2)

INSERT INTO Table1 (Column1, Column2)
VALUES(1, 2)

INSERT INTO Table1 (Column1, Column2)
VALUES(1, @var2)

INSERT INTO Table1 (Column1, Column2)
SELECT @var1, @var2

INSERT INTO Table1 (Column1, Column2)
SELECT 1, 2

INSERT INTO Table1 (Column1, Column2)
SELECT 1, @var2

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2
FROM Table2

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, 'SomeValue'
FROM Table2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 14:49:33

Thanks for your help I fixed the query but still getting an error message. This is what I'm looking to do...

I have one master table called SSIDIiaries

I created two tables: WeeklyDiariesFile and AllDiaries

Here are the fields:
WeeklyDiariesFile
WeeklyId indentity column
area
dist
doc
pan
fname
lname
week_start_date

The other table:
AllDiaries
AlldiariesID identity column
WeeklyID (want to get this from the WeeklyDiariesFile table)
hun
recno
week_start_date
drycde
drydat
dryfu
curstat

I would like to have the records I need pulled from the SSIDiaries to go into the WeeklyDiariesFile Table by using the first procedure listed above.

Then I would like for that WeeklyID (from the WeeklyDiariesFile) and the record that goes with it to go into the AllDiaries Table along with the other fields I've specified above. I wonder do I need to do a join on the SSIDiaries Table for the last procedure since that table has all of the files I need?

I tried this but getting incorrect syntax near ',' line 36 which is in bold below

set NOCOUNT on
declare @weeklyid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

--DECLARE @TRANCOUNT int
--SET @TRANCOUNT = @@TRANCOUNT
--Begin Tran TranStart

insert into WeeklyDiariesFile
(area, dist, doc, pan, fname, lname, week_start_date)

select
area,
dist,
doc,
pan,
fname,
lname,
convert(nvarchar,@dte,101)
from SSIDiaries

set @weeklyid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

insert into AllDiaries(weeklyid,hun, recno, curstat, week_start_date, drycde, drydat, dryfu)

select
(weeklyid,
hun,
recno,
curstat,
week_start_date,
drycde,
drydat,
dryfu)

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-06 : 15:07:11

You've added parenthesis around the select, which is not correct. You are also missing the FROM clause.

INSERT INTO ... (...)
SELECT ...
FROM ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-06 : 15:59:51

Okay I got some of the data but I getting this error message:

Cannot insert the value NULL into column 'WeeklyID', table 'OffsetsSQL.dbo.WeeklyDiariesFile'; column does not allow nulls. INSERT fails.

There is no data in my WeeklyDiariesFile

But I have the correct data in my AllDiaries file minus the WeeklyID. Can you tell me what I'm doing wrong:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[BeaTest]
As
set NOCOUNT on
declare @weeklyid int
declare @alldiariesid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart

insert into WeeklyDiariesFile
(area, dist, doc, pan, fname, lname, week_start_date)
select
area,
dist,
doc,
pan,
fname,
lname,
convert(nvarchar,@dte,101)
from SSIDiaries

set @weeklyid = SCOPE_IDENTITY() --grabbing the id

--select weeklyid = @weeklyid

if @@error<>0
Begin
Rollback Transaction
Return
End

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)

select @weeklyid as weeklyid,
hun,
recno,
drycd1 as drycde,
drydte1 as drydte,
dryfu1 as dryfu,
curstat,
convert(nvarchar,@dte,101)

From SSIDiaries
where drycd1 !=' '
set @alldiariesid = SCOPE_IDENTITY() --grabbing the id

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)

select @weeklyid as weeklyid,
hun,
recno,
drycd2 as drycde,
drydte2 as drydte,
dryfu2 as dryfu,
curstat,
convert(nvarchar,@dte,101)

From SSIDiaries
where drycd2 !=' '

set @alldiariesid = SCOPE_IDENTITY() --grabbing the id

if @@error<>0
Begin
Rollback Transaction
Return
End

Commit Tran Transtart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-06 : 18:25:00

The error indicates you are having a problem here, since it's the only place I see you doing an insert into the table mentioned in the error:

insert into WeeklyDiariesFile
(area, dist, doc, pan, fname, lname, week_start_date)
select
area,
dist,
doc,
pan,
fname,
lname,
convert(nvarchar,@dte,101)
from SSIDiaries

Since you have excluded weeklyid from the column list, I'll assume you think weeklyid is an identity column. But the fact that you are getting that error means it isn't set to identity!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-07 : 08:37:31

Perfect that was it!!!! Thanks. I'll be back in a few with another question. Almost there... The top procedure is working now have to work on the bottom one.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-07 : 09:25:20

Okay my problem is now with the AllDiaries section of this query. I have three diaries I am working with.

I would like for the WeeklyID to go into the AllDiaries Table. What's going in there is the last record number/count that's in the AllDiaries Table there are 802302 records in the AllDiaries Table. So in the weeklyid field in the AllDiaries Table it has 802302 for all of the weeklyid's.

The other records are going into all of the fields of the AllDiaries Table but can't tell if they are right or not because I don't have the weeklyid. Does that make sense?

set NOCOUNT on

declare @weeklyid int
set @weeklyid = 1
declare @alldiariesid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

insert into WeeklyDiariesFile
(area, dist, doc, pan, fname, lname, week_start_date)
select
area,
dist,
doc,
pan,
fname,
lname,
convert(nvarchar,@dte,101)
from SSIDiaries

Select @weeklyID=@@Identity

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @weeklyid as weeklyid,
hun,
recno,
drycd1 as drycde,
drydte1 as drydte,
dryfu1 as dryfu,
curstat,
convert(nvarchar,@dte,101)
From SSIDiaries
where drycd1 !=' '

set @alldiariesid = SCOPE_IDENTITY() --grabbing the id

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @weeklyid as weeklyid,
hun,
recno,
drycd2 as drycde,
drydte2 as drydte,
dryfu2 as dryfu,
curstat,
convert(nvarchar,@dte,101)
From SSIDiaries
where drycd2 !=' '
set @alldiariesid = SCOPE_IDENTITY() --grabbing the id

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @weeklyid as weeklyid,
hun,
recno,
drycd3 as drycde,
drydte3 as drydte,
dryfu3 as dryfu,
curstat,
convert(nvarchar,@dte,101)
From SSIDiaries
where drycd3 !=' '
set @alldiariesid = SCOPE_IDENTITY() --grabbing the id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources