Append records and increment field

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)

Append records and increment field

Author

Topic

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-23 : 15:07:14

I need to append some records to a table and append an incrementing number at the same time.

I need to be able to start the incrementing number at a specific value that is gotten from doing a max of a field from another table. The number will be incremented by one everytime as i append the records to the temp table.

For example:

TableA:
Field: NumberA (has numbers in it and the highest number is 38.)

TableB:
Field: FirstName, LastName

TableTemp:
Fields: Counter (starting at 39-->from TableA), FirstName, LastName

I can get the max value (39 in this example) but don't know how to increment it by one each time as I append my records.

Can this be done in one procedure or do I need multiple ones? Either way, can soemone give me examples of how to do it, becuse I've spent about a week on this and can't get it.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 15:22:44

select FirstName, LastName, identity(int, 1, 1) as id into #a from TableB
declare @id int
select @id = max(NumberA) from TableA
insert TableTemp (Counter, FirstName, LastName)
select @id + id, FirstName, LastName
from #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-23 : 15:44:30

Thanks, I was just reading about the identity function when you reply came in. I will give it a try and report back.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-23 : 16:18:45

Here is what I have thus far:

SELECT ItemID,  ItemType,  EquipDesc, identity(int, 1, 1) as id into #a FROM vSchedule_SchedulableItems ORDER BY ItemType, EquipDesc
declare @id int
select @id = max(ItemID) from T_Schedule_ProductionItems
INSERT INTO zzT_Schedule_BatchItems ( OrigID,  ItemType,  ItemDescription,  ItemID )
select ItemID,  ItemType,  EquipDesc, @id + id
from #a

It runs without error, but it does not seem to create the table.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-24 : 17:41:29

I had to create the table first.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-24 : 17:51:44

I'm on the the next, and last, stored procedure that needs a counter, but this one uses several tables and I am getting lost in it all.

Here is the un-edited version:

 INSERT INTO dbo.T_Schedule_BatchSchedule
                      (ScheduleID, ItemID, EndDate, EndTime, StartDate, StartTime, BarStyle, BarText, OrigID, WOID, WOIDSub)
SELECT     '7777' AS Counter2, tSBI.ItemID, vSTHBW.DueDate, '0' AS TimeEnd, CONVERT(varchar, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate), 101) 
                      AS DateStart, DATEPART(hh, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate)) * 60 + DATEPART(mm, DATEADD(n, - vSTHBW.TotalMin, 
                      vSTHBW.DueDate)) AS StartMin, '7' AS BarStyle, '‡ ' + CAST(vSTHBW.WOID AS varchar(30)) 
                      + ',  ' + dbo.tblArCust.CustName + ',  ' + tSSHH.PartDescription AS BarText, tSBI.OrigID, tSSHH.WOID, tSSHH.WOIDSub
FROM         dbo.tblArCust INNER JOIN
                      dbo.T_SetupSheetHistoryHeader tSSHH INNER JOIN
                      dbo.vSchedule_TotalHoursByWOID vSTHBW ON vSTHBW.WOIDSub = tSSHH.WOIDSub AND tSSHH.Revision = vSTHBW.Revision AND 
                      tSSHH.ECN = vSTHBW.ECN AND tSSHH.WOID = vSTHBW.WOID AND tSSHH.QuoteID = vSTHBW.QuoteID INNER JOIN
                      dbo.vSchedule_ExtrudersAndTooling vSEAT ON tSSHH.WOIDSub = vSEAT.WOIDSub AND tSSHH.Revision = vSEAT.Revision AND 
                      tSSHH.ECN = vSEAT.ECN AND tSSHH.WOID = vSEAT.WOID AND tSSHH.QuoteID = vSEAT.QuoteID INNER JOIN
                      dbo.T_Schedule_BatchItems tSBI ON vSEAT.ItemID = tSBI.OrigID ON dbo.tblArCust.CustId = tSSHH.CustId
WHERE     (tSSHH.Scheduled = 1)

The 7777 part is where I need the counter to go. Here is what have so far:

Select ItemID,  ItemType,  ItemDescription, identity(int, 1, 1) as id into #zzAppendToSchedualBatch
FROM T_Schedule_BatchItems
ORDER BY ItemType, ItemDescription

declare @id int
select @id = max(ItemID) from ProductionSchedule

 INSERT INTO dbo.T_Schedule_BatchSchedule
                      (ScheduleID, ItemID, EndDate, EndTime, StartDate, StartTime, BarStyle, BarText, OrigID, WOID, WOIDSub)
SELECT      @id + id AS Counter2, tSBI.ItemID, vSTHBW.DueDate, '0' AS TimeEnd, CONVERT(varchar, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate), 101) 
                      AS DateStart, DATEPART(hh, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate)) * 60 + DATEPART(mm, DATEADD(n, - vSTHBW.TotalMin, 
                      vSTHBW.DueDate)) AS StartMin, '7' AS BarStyle, '‡ ' + CAST(vSTHBW.WOID AS varchar(30)) 
                      + ',  ' + dbo.tblArCust.CustName + ',  ' + tSSHH.PartDescription AS BarText, tSBI.OrigID, tSSHH.WOID, tSSHH.WOIDSub
FROM         dbo.tblArCust INNER JOIN
                      dbo.T_SetupSheetHistoryHeader tSSHH INNER JOIN
                      dbo.vSchedule_TotalHoursByWOID vSTHBW ON vSTHBW.WOIDSub = tSSHH.WOIDSub AND tSSHH.Revision = vSTHBW.Revision AND 
                      tSSHH.ECN = vSTHBW.ECN AND tSSHH.WOID = vSTHBW.WOID AND tSSHH.QuoteID = vSTHBW.QuoteID INNER JOIN
                      dbo.vSchedule_ExtrudersAndTooling vSEAT ON tSSHH.WOIDSub = vSEAT.WOIDSub AND tSSHH.Revision = vSEAT.Revision AND 
                      tSSHH.ECN = vSEAT.ECN AND tSSHH.WOID = vSEAT.WOID AND tSSHH.QuoteID = vSEAT.QuoteID INNER JOIN
                      dbo.T_Schedule_BatchItems tSBI ON vSEAT.ItemID = tSBI.OrigID ON dbo.tblArCust.CustId = tSSHH.CustId
WHERE     (tSSHH.Scheduled = 1)

But where does the "from #zzAppendToSchedualBatch" go? Does it get joined or what?

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-05-26 : 10:40:45

Well I got it to work by using this:

SELECT       identity(int, 1, 1) as id, tSBI.ItemID, vSTHBW.DueDate, '0' AS TimeEnd, CONVERT(varchar, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate), 101) 
                      AS DateStart, DATEPART(hh, DATEADD(n, - vSTHBW.TotalMin, vSTHBW.DueDate)) * 60 + DATEPART(mm, DATEADD(n, - vSTHBW.TotalMin, 
                      vSTHBW.DueDate)) AS StartMin, '7' AS BarStyle, '‡ ' + CAST(vSTHBW.WOID AS varchar(30)) + ',  ' + dbo.tblArCust.CustName + ',  ' + tSSHH.PartDescription AS BarText, 
		tSBI.OrigID, tSSHH.WOID, tSSHH.WOIDSub
into #zzAppendToSchedualBatch
FROM         dbo.tblArCust INNER JOIN
                      dbo.T_SetupSheetHistoryHeader tSSHH INNER JOIN
                      dbo.vSchedule_TotalHoursByWOID vSTHBW ON vSTHBW.WOIDSub = tSSHH.WOIDSub AND tSSHH.Revision = vSTHBW.Revision AND 
                      tSSHH.ECN = vSTHBW.ECN AND tSSHH.WOID = vSTHBW.WOID AND tSSHH.QuoteID = vSTHBW.QuoteID INNER JOIN
                      dbo.vSchedule_ExtrudersAndTooling vSEAT ON tSSHH.WOIDSub = vSEAT.WOIDSub AND tSSHH.Revision = vSEAT.Revision AND 
                      tSSHH.ECN = vSEAT.ECN AND tSSHH.WOID = vSEAT.WOID AND tSSHH.QuoteID = vSEAT.QuoteID INNER JOIN
                      dbo.T_Schedule_BatchItems tSBI ON vSEAT.ItemID = tSBI.OrigID ON dbo.tblArCust.CustId = tSSHH.CustId
WHERE     (tSSHH.Scheduled = 1)


declare @id int
select @id = max(ItemID) from T_ProductionSchedule

INSERT INTO dbo.T_Schedule_BatchSchedule (ScheduleID, ItemID, EndDate, EndTime, StartDate, StartTime, BarStyle, BarText, OrigID, WOID, WOIDSub)
select  @id + id, ItemID, DueDate, TimeEnd,  DateStart, StartMin, BarStyle, BarText, OrigID, WOID, WOIDSub

from #zzAppendToSchedualBatch

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources