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.
| Author |
Topic |
|
expat
Starting Member
7 Posts |
 Posted - 2010-07-01 : 22:12:18
|
| HiI use a sp to insert/update records. Parsing a DataTable (many) to a user-define table typesTable type:CREATE TYPE [dbo].[SeasonType] AS TABLE( [SeasonTypeId] [int] NULL, [RateSheetId] [int] NULL, [SeasonRateId] [int] NULL, [SeasonName] [nvarchar](100) NULL, [FromDate] [datetime] NULL, [ToDate] [datetime] NULL)Table to Insert/Update:CREATE TABLE [dbo].[Season]( [SeasonID] [int] IDENTITY(1,1) NOT NULL, [RateSheetId] [int] NOT NULL, [SeasonRatetId] [int] NOT NULL, [SeasonName] [nvarchar](100) NOT NULL, [FromDate] [datetime] NOT NULL, [ToDate] [datetime] NOT NULL, CONSTRAINT [PK_Season] PRIMARY KEY CLUSTERED ( [SeasonID] ASC, [RateSheetId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Will this work (snipp from sp)--Insert/Update Season beginINSERT INTO Season (Season.RateSheetId, Season.SeasonRatetId, Season.SeasonName, Season.FromDate, Season.ToDate)SELECT @SheetId, SeasonRateId, SeasonName, FromDate, ToDate FROM @SeasonWHERE RateSheetId NOT IN (SELECT RateSheetId FROM Season) AND SeasonName NOT IN (SELECT SeasonName FROM Season)SELECT @SeasonId = SCOPE_IDENTITY() UPDATE SeasonSET Season.SeasonName = SeasonName, Season.FromDate = FromDate, Season.ToDate = ToDateSELECT SeasonName, FromDate, ToDate FROM @Season --Insert/Update Season end |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
|
|
|
|
|
Insert/update