January 26, 2015 at 1:57 pm
the idea is we have a table that has available coupon codes from there different locations. I need to select from all three environments and get the top one that has UsedFlag<>1. i then need to set the usedFlag = 1 in all environments. All three locations will be doing the same thing. i was going to use a tran w/ linked server however was curious if anyone had a better way of advice.
/****** Object: Table [dbo].[CouponCodes] Script Date: 1/26/2015 3:52:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CouponCodes](
[CouponCodeID] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CouponCode] [NVARCHAR](50) NOT NULL,
[DealID] [BIGINT] NOT NULL,
[UsedFlag] [VARCHAR](5) NOT NULL CONSTRAINT [DF_CouponCodes_UsedFlag] DEFAULT ('False'),
CONSTRAINT [PK_CouponCodes] PRIMARY KEY CLUSTERED
(
[CouponCodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
January 26, 2015 at 2:01 pm
i'm thinking i'm going to wrap all three updates in a tran and have some sort of retry logic if all three arent updated successfully
January 26, 2015 at 2:03 pm
Snargables (1/26/2015)
the idea is we have a table that has available coupon codes from there different locations. I need to select from all three environments and get the top one that has UsedFlag<>1. i then need to set the usedFlag = 1 in all environments. All three locations will be doing the same thing. i was going to use a tran w/ linked server however was curious if anyone had a better way of advice./****** Object: Table [dbo].[CouponCodes] Script Date: 1/26/2015 3:52:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CouponCodes](
[CouponCodeID] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CouponCode] [NVARCHAR](50) NOT NULL,
[DealID] [BIGINT] NOT NULL,
[UsedFlag] [VARCHAR](5) NOT NULL CONSTRAINT [DF_CouponCodes_UsedFlag] DEFAULT ('False'),
CONSTRAINT [PK_CouponCodes] PRIMARY KEY CLUSTERED
(
[CouponCodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Why is UsedFlag a varchar with a default of False? This looks like a good candidate for a bit column. Also, do you use characters outside the ASCII character set as coupon codes? If not, maybe that should be varchar instead?
As for your question, you need the top one. Based on what criteria? I don't see anything in here that says which of your three environments this comes from. Do have 1 set of coupons but maintain 3 copies of the data? So you have coupon code "Next10" in all three databases and you want to update all of them to indicate they are used? If so, why not have just a single location for coupons? Your question is good but there just aren't enough details here for us to do much more than guess.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2015 at 6:47 am
lol. I didnt create this table and assumed the used flag was a big. the table has several more cols. I just pulled ones that matter for this discussion and put it in there. I'll send what im thinking about doing in a sec. It will be more clear what i'm trying to do. sorry for the confussion.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply