May 28, 2014 at 12:11 pm
I want to insert many rows at a time into two tables - I have a ValidationMessage Table with the ValidationMessageID and the message, and then an OrderValidationMessages table with the OrderID and the ValidationMessageID
right now the system goes order by order and insert validation messages for each order -and I was thinking I could speed it up by doing a whole result that failed validation at one time - such as:
Insert Into ValidationMessage(ValidationMessageTypeID, Text)
Output Inserted.ValidationMessageID, O.OrderID into OrderValidationMessage (ValidationMessageID, OrderID)
select 2, 'Customer Name Cannot be GAYLE' from [Order] O
where agentbatchID = 29216 AND CustomerName like '%GAYLE%'
but of course O.OrderID is not available on the Output line 🙁
Any Ideas? rewriting the table structure is not possible 😉
Thanks!
Jamie
May 28, 2014 at 1:23 pm
jamie 35248 (5/28/2014)
I want to insert many rows at a time into two tables - I have a ValidationMessage Table with the ValidationMessageID and the message, and then an OrderValidationMessages table with the OrderID and the ValidationMessageIDright now the system goes order by order and insert validation messages for each order -and I was thinking I could speed it up by doing a whole result that failed validation at one time - such as:
Insert Into ValidationMessage(ValidationMessageTypeID, Text)
Output Inserted.ValidationMessageID, O.OrderID into OrderValidationMessage (ValidationMessageID, OrderID)
select 2, 'Customer Name Cannot be GAYLE' from [Order] O
where agentbatchID = 29216 AND CustomerName like '%GAYLE%'
but of course O.OrderID is not available on the Output line 🙁
Any Ideas? rewriting the table structure is not possible 😉
Thanks!
Jamie
Hi and welcome to the forums. I would be willing to help but what you posted doesn't make much sense. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
May 28, 2014 at 1:34 pm
HI Sean - Thanks for the reply - the two tables are pretty simple:
CREATE TABLE [dbo].[ValidationMessage](
[ValidationMessageID] [bigint] IDENTITY(1,1) NOT NULL,
[ValidationMessageTypeID] [int] NOT NULL,
[Text] [nvarchar](max) NOT NULL,
[ToBeDeleted] [bit] NOT NULL,
CONSTRAINT [PK_ValidationMessage] PRIMARY KEY CLUSTERED
(
[ValidationMessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[OrderValidationMessage](
[OrderID] [int] NOT NULL,
[ValidationMessageID] [bigint] NOT NULL,
CONSTRAINT [PK_OrderValidationMessage] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[ValidationMessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
basically what I want to do is run a query that will get multiple orders that fail a validation rule, insert the message into the ValidationMessages and then insert the connecting record in the OrderValidationMessages - I get everything except the OrderID in my query in my original post
May 28, 2014 at 1:40 pm
How about the Order table and a few rows of sample data? I think I see where you are trying to go but want to be sure and with tables I can help you write the query. 😉
_______________________________________________________________
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/
May 28, 2014 at 1:51 pm
The order table is :
CREATE TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[AgentBatchID] [int] NOT NULL,
[FulfillmentCenterBatchID] [int] NULL,
[CustomerName] [nvarchar](50) NULL,
[AddressID] [int] NULL,
[Phone] [nvarchar](50) NULL,
[ProductID] [int] NULL,
[UnitAgentRemit] [money] NULL,
[UnitAgentBonus] [money] NULL,
[ChannelID] [int] NULL,
[Quantity] [int] NULL,
[ReferenceID] [nvarchar](50) NULL,
[SubAgentEIN] [nvarchar](50) NULL,
[OrderStatusID] [int] NOT NULL,
[DateFulfilled] [datetime] NULL,
[DateRevoked] [datetime] NULL,
[DateRejected] [datetime] NULL,
[IsRenewal] [bit] NOT NULL,
[UnitRemit] [money] NULL,
[UnitBonus] [money] NULL,
[TotalAgentRemit] AS ([Quantity]*[UnitAgentRemit]),
[TotalAgentBonus] AS ([Quantity]*[UnitAgentBonus]),
[TotalRemit] AS ([Quantity]*[UnitRemit]),
[TotalBonus] AS ([Quantity]*[UnitBonus]),
[IsSpecialAgentBonus] [bit] NOT NULL,
[DateReleased] [datetime] NULL,
[DatePaid] [datetime] NULL,
[ReasonRevoked] [varchar](50) NULL,
[Email] [nvarchar](75) NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
in the ValidationMessages I expect to see:
ValidationMessageID Message
150439 New address could not be found.
150440 New address could not be found.
215195 New address could not be found.
215196 New address could not be found.
215197 New address could not be found.
215198 New address could not be found.
and in the OrderValidationMessages I expect to see:
ValidationMessageID OrderID
150439 508335
150440 508336
215195 508337
215196 540736
215197 540736
215198 540875
Hope that all makes sense 🙂 For the record this is an inherited system 😉
May 28, 2014 at 2:22 pm
Any chance you can turn that into insert statements? Also, need at least a row or two for Orders (that is in your select statement ). 😀
I am still rather lost on what you are trying to do exactly.
_______________________________________________________________
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/
May 28, 2014 at 5:19 pm
You can do what you describe, it's called Composable DML. This is much more powerful with the MERGE statement where you can access columns from the inner MERGE which aren't in the inserted or deleted tables. Work through this demo, see if it makes sense to you:
DECLARE @ValidationMessage TABLE (
[ValidationMessageID] [bigint] IDENTITY(1000,1) PRIMARY KEY,
[ValidationMessageTypeID] [int] NOT NULL,
[Text] [nvarchar](max) NOT NULL,
[ToBeDeleted] [bit] NOT NULL
)
DECLARE @OrderValidationMessage TABLE (
[OrderID] [int] NOT NULL,
[ValidationMessageID] [bigint] NOT NULL,
PRIMARY KEY ( [OrderID], [ValidationMessageID] )
)
DECLARE @Order TABLE (
[OrderID] [int] PRIMARY KEY,-- IDENTITY(1,1)
[OrderDate] [datetime] NOT NULL DEFAULT GETDATE(),
[AgentBatchID] [int] NOT NULL,
[FulfillmentCenterBatchID] [int] NULL,
[CustomerName] [nvarchar](50) NULL,
[AddressID] [int] NULL,
[Phone] [nvarchar](50) NULL,
[ProductID] [int] NULL,
[UnitAgentRemit] [money] NULL,
[UnitAgentBonus] [money] NULL,
[ChannelID] [int] NULL,
[Quantity] [int] NULL,
[ReferenceID] [nvarchar](50) NULL,
[SubAgentEIN] [nvarchar](50) NULL,
[OrderStatusID] [int] NOT NULL DEFAULT 1,
[DateFulfilled] [datetime] NULL,
[DateRevoked] [datetime] NULL,
[DateRejected] [datetime] NULL,
[IsRenewal] [bit] NOT NULL DEFAULT 0,
[UnitRemit] [money] NULL,
[UnitBonus] [money] NULL,
[TotalAgentRemit] AS ([Quantity]*[UnitAgentRemit]),
[TotalAgentBonus] AS ([Quantity]*[UnitAgentBonus]),
[TotalRemit] AS ([Quantity]*[UnitRemit]),
[TotalBonus] AS ([Quantity]*[UnitBonus]),
[IsSpecialAgentBonus] [bit] NOT NULL DEFAULT 0,
[DateReleased] [datetime] NULL,
[DatePaid] [datetime] NULL,
[ReasonRevoked] [varchar](50) NULL,
[Email] [nvarchar](75) NULL
)
-- Generate some dummy order data
INSERT INTO @Order ( agentbatchID, OrderID, CustomerName )
SELECT x.agentbatchID, y.OrderID, x.CustomerName
FROM
(
VALUES
( 29216, 'Gayle' )
) x( agentbatchID, CustomerName )
CROSS JOIN
(
VALUES
( 508335 ), ( 508336 ), ( 508337 ), ( 540736 ), ( 540875 )
) y( OrderID )
-- Insert to OrderValidationMessage and ValidationMessage at the same time
INSERT INTO @OrderValidationMessage ( OrderID, ValidationMessageID )
SELECT OrderID, ValidationMessageID
FROM
(
MERGE @ValidationMessage [target]
USING
(
SELECT
OrderId,
2 AS ValidationMessageTypeID,
'Customer Name Cannot be GAYLE' AS [Text],
0 AS ToBeDeleted
FROM [Order] O
WHERE agentbatchID = 29216
AND CustomerName like '%GAYLE%'
) AS [source] ON 1 = 2-- dummy condition
WHEN NOT MATCHED
THEN INSERT ( ValidationMessageTypeID, [Text], ToBeDeleted )
VALUES ( ValidationMessageTypeID, [Text], ToBeDeleted )
OUTPUT inserted.ValidationMessageID, [source].OrderId
) x
SELECT * FROM @Order
SELECT * FROM @ValidationMessage
SELECT * FROM @OrderValidationMessage
GO
June 2, 2014 at 10:09 am
The composable DML looks interesting. I'm curious to look at it's execution plan and see how it performs with large batches.
An alternative I've seen involved a cursor used to read one table (with several hundred thousand rows) and write to three different tables. In that case, simply writing three different INSERT queries proved to be much faster. Most of the input data remained in cache, minimizing the number of physical reads. Lessons learned? Avoid cursors and don't be afraid to read a table twice.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 3, 2014 at 7:27 am
Sorry for the lag in response and thanks for the replies and ideas!
I will be playing with this again this week, so I'll post some progress soon 🙂
Thanks
Jamie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply