April 22, 2009 at 1:23 pm
I need to insert a record into a table but the id column is not set to auto grow and I dont have control over database. So I created a SP to get the Max ID. Now I need to take this max id from SP and fireup the another insert statement.
Here is my SP to get the max id
Select max(holiday_number) from HOLIDAY_DESCRIPTION
select SCOPE_IDENTITY()
How do I create another Store proc that will take this id +1 for an insert statement?
This is my select statement where I need to verify record count >=1 then fireup insert statement. (insert statement will be very much like following select statement except it will need to get the maxID and set holiday_number= maxid+1).
SELECT holiday_description hDesc, month(holiday_date) hMonth
from HOLIDAY_SCHEDULE HS
where NOT EXISTS
(select holiday_description from HOLIDAY_DESCRIPTION hd
WHERE HS.holiday_description=hd.holiday_description)
April 22, 2009 at 1:45 pm
doran_doran (4/22/2009)
I need to insert a record into a table but the id column is not set to auto grow and I dont have control over database. So I created a SP to get the Max ID. Now I need to take this max id from SP and fireup the another insert statement.Here is my SP to get the max id
Select max(holiday_number) from HOLIDAY_DESCRIPTION
select SCOPE_IDENTITY()
This sp is going to return two recordsets. The select scope_identity() is totally unnecessary here.
How do I create another Store proc that will take this id +1 for an insert statement?
This is my select statement where I need to verify record count >=1 then fireup insert statement. (insert statement will be very much like following select statement except it will need to get the maxID and set holiday_number= maxid+1).
SELECT holiday_description hDesc, month(holiday_date) hMonth
from HOLIDAY_SCHEDULE HS
where NOT EXISTS
(select holiday_description from HOLIDAY_DESCRIPTION hd
WHERE HS.holiday_description=hd.holiday_description)
I suggest you follow the guidelines in the link in my signature, and supply some table DDL for each of the tables, and some test data via insert statements into those tables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 2:01 pm
I have attached sql of the tables with data. Please let me know. Thanks for all the help. I truely appreciate it.
Here is my goal.
1. Determine where I need to insert or not (from the not exists statement)
2. Insert if there any row (from item 1)
I hope it makes sense.
April 22, 2009 at 3:35 pm
Hi Doran
As first, I took your attached SQL Scripts and just surround with [ code ] and [ /code ] (no spaces) for better reading for other people ;-).
CREATE TABLE [dbo].[HOLIDAY_DESCRIPTION](
[holiday_number] [bigint] NOT NULL,
[holiday_description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[holimonth] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[HOLIDAY_SCHEDULE](
[schedule_number] [bigint] NOT NULL,
[holiday_date] [smalldatetime] NOT NULL,
[holiday_description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
As second:
I would suggest to use either an id-table instead of MAX or include the MAX()+1 within your insert statement. Don't forget that a SQL Server is a multi-threading application. There can be other inserts between your MAX select and your insert.
Greets
Flo
April 22, 2009 at 3:35 pm
wayne, where r u? I attached both sql to create tables and data. I can create exact logic in .net but I like to do it in sql server as I dont really need over burden my .net application.
April 22, 2009 at 3:38 pm
doran_doran (4/22/2009)
wayne, where r u? I attached both sql to create tables and data. I can create exact logic in .net but I like to do it in sql server as I dont really need over burden my .net application.
At lunch, at home, in a meeting or maybe just reading a book. Nobody is payed here so nobody needs to be always available 😉
Greets
Flo
April 22, 2009 at 3:55 pm
That's funny. LOL.
How come people have all the time to comment on how to post?
Who really cares as long as it's not all that offensive?
I need some solution, I googled it first but failed. At last I turn to this forum and all I get bunch of empty lectures on how to post.
Please help if you can else dont make empty comments. Working on a deadline and dont have time for comments.
Thanks
April 22, 2009 at 6:19 pm
doran_doran (4/22/2009)
wayne, where r u? I attached both sql to create tables and data. I can create exact logic in .net but I like to do it in sql server as I dont really need over burden my .net application.
uhhh, I'm spending time with my family. Which is the #1 priority in my life.
Do you have any test data to throw into those tables? Then there will be something to test against.
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 6:50 pm
Wayne,
Sorry. Didnt mean to take you away from your family time. NO doubt family is first. That is why I am going to do this in c# .net cause of the time constrain. However, I did attached Create SQL with Data in my previous post (if_PostLink('http://www.sqlservercentral.com/Forums/FindPost702708.aspx'))
Thanks again for your help.
April 22, 2009 at 7:40 pm
Okay, how's this?
-- create temporary table
DECLARE @HOLIDAY_DESCRIPTION TABLE (
[holiday_number] [bigint] NOT NULL PRIMARY KEY CLUSTERED,
[holiday_description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[holimonth] [int] NULL )
-- create temporary table
DECLARE @HOLIDAY_SCHEDULE TABLE (
[schedule_number] [bigint] NOT NULL PRIMARY KEY CLUSTERED,
[holiday_date] [smalldatetime] NOT NULL UNIQUE,
[holiday_description] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL )
-- put some test data into the tables
insert into @HOLIDAY_DESCRIPTION
select 1, 'New Year''s Day', 1 UNION
select 2, 'Easter', 4 UNION
select 3, 'Memorial Day', 5 UNION
select 4, 'Independence Day', 6 UNION
select 5, 'Labor Day', 9 UNION
select 6, 'Thanksgiving Day', 11 UNION
select 7, 'Christmas Day', 12
insert into @HOLIDAY_SCHEDULE
select 1, '1/1/2009', 'New Year''s Day' UNION
select 2, '2/16/2009', 'President''s Day' UNION
select 3, '5/25/2009', 'Memorial Day' UNION
select 4, '7/4/2009', 'Independence Day' UNION
select 5, '9/7/2009', 'Labor Day' UNION
select 6, '11/26/2009', 'Thanksgiving Day' UNION
select 7, '12/25/2009', 'Christmas Day' UNION
select 8, '2/14/2009', 'Valentine''s Day' UNION
select 9, '1/19/2009', 'Martin Luther King''s Birthday' UNION
select 10, '4/12/2009', 'Easter' UNION
select 11, '10/12/2009', 'Columbus Day' UNION
select 12, '11/11/2009', 'Veteran''s Day'
First, I took your existing table structures and made them in into temporary tables.
Then I insert some sample data.
Having the DDL and DML code like this makes it easier for those helping to get straight to the job.
-- show the holiday descriptions in the table
select * from @HOLIDAY_DESCRIPTION
-- let's get rid of that whole stored procedure to get the next number,
-- and just get that last number ourselves.
declare @max_holiday_number bigint
select @max_holiday_number = max(holiday_number) from @HOLIDAY_DESCRIPTION
-- get the holiday descriptions and month from the holiday_schedule table
-- that are not in the holiday_description table
-- assign row numbers (row number of the results, plus the max # from above) to each of these
-- insert all of these missing holidays into the holiday_description table
INSERT INTO @HOLIDAY_DESCRIPTION
SELECT row_number() OVER(ORDER BY holiday_description) + @max_holiday_number RN, holiday_description hDesc, month(holiday_date) hMonth
from @HOLIDAY_SCHEDULE HS
where NOT EXISTS
(select holiday_description from @HOLIDAY_DESCRIPTION hd
WHERE HS.holiday_description=hd.holiday_description)
-- show that these have been added with new valid holiday_number's
select * from @HOLIDAY_DESCRIPTION
Now, as others have already mentioned earlier, it would be considerably easier if the HOLIDAY_DESCRIPTION.holiday_number and HOLIDAY_SCHEDULE.schedule_number columns were assigned to be an IDENTITY column. If this were the case, then you could simply do:
INSERT INTO @HOLIDAY_DESCRIPTION
SELECT holiday_description hDesc, month(holiday_date) hMonth
from @HOLIDAY_SCHEDULE HS
where NOT EXISTS
(select holiday_description from @HOLIDAY_DESCRIPTION hd
WHERE HS.holiday_description=hd.holiday_description)
HTH,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 8:58 pm
Hello Wayne,
YOU ARE TOOOOOOO GOOD AND GENEROUS OF YOUR TIME. I HAVE NO IDEA HOW I AM GOING TO THANK YOU.
It worked like charm. You could off spend more time with your family but you helped me. I TRUELY APPRECIATE IT.
Bless.
Again, thanks a billion for your time, help, education.
Here is the final version and working.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Add2HolidayDesc]
@max_holiday_number bigint
as
begin
select @max_holiday_number = max(holiday_number) from HOLIDAY_DESCRIPTION
INSERT INTO HOLIDAY_DESCRIPTION
SELECT row_number() OVER(ORDER BY holiday_description) + @max_holiday_number RN, holiday_description hDesc, month(holiday_date) hMonth
from HOLIDAY_SCHEDULE HS
where NOT EXISTS
(select holiday_description from HOLIDAY_DESCRIPTION hd
WHERE HS.holiday_description=hd.holiday_description)
end
go
April 23, 2009 at 7:24 pm
doran_doran (4/22/2009)
That's funny. LOL.How come people have all the time to comment on how to post?
Who really cares as long as it's not all that offensive?
I need some solution, I googled it first but failed. At last I turn to this forum and all I get bunch of empty lectures on how to post.
Please help if you can else dont make empty comments. Working on a deadline and dont have time for comments.
Thanks
Heh... empty comments. Now THAT's funny. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply