May 7, 2011 at 2:57 pm
I was setting up to do a demo on how to eliminate duplicate records from a heap and, after reading and experimenting from Jeff Moden's great articles on tally tables, I thought: I'll use a tally table instead of a loop to insert the duplicate records into the heap.
I thought I might be able to do something like this to insert 20 duplicate rows:
SELECT N
INSERT tblDupesDemo_ccb VALUES ('ABCDEF',10,125.50)
FROM tblTally_ccb
WHERE N < 21
But I get the error message: Incorrect syntax near the keyword 'FROM'
Can one use a tally table in this fashion?
Thanks in advance for any help.
Christian Bahnsen
May 7, 2011 at 3:21 pm
Insert SELECT, not insert VALUEs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 8, 2011 at 12:58 am
My numbers (or 'tally') table has a different name from yours, but the following code illustrates another way to write this query. I wasn't sure if you wanted to return the inserted rows to the caller as well as adding them to the target table; if that is not required, just remove the OUTPUT clause.
DECLARE @Destination TABLE
(
string_value CHAR(6) NOT NULL,
integer_value INTEGER NOT NULL,
decimal_value DECIMAL(5,2) NOT NULL
);
INSERT @Destination
(
string_value,
integer_value,
decimal_value
)
OUTPUT
inserted.string_value,
inserted.integer_value,
inserted.decimal_value
SELECT
SampleData.string_value,
SampleData.integer_value,
SampleData.decimal_value
FROM
(
VALUES
(
CONVERT(CHAR(6), 'ABCDEF'),
CONVERT(INTEGER, 10),
CONVERT(DECIMAL(5,2), 125.50)
)
) AS SampleData
(
string_value,
integer_value,
decimal_value
)
CROSS JOIN dbo.Numbers AS N
WHERE
N.n >= 1
AND N.n <= 20;
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 9, 2011 at 4:11 am
Similarly:
DECLARE @t TABLE(s CHAR(6), i INT, d decimal(19,5))
INSERT @t(s,i,d)
SELECT TOP 20
'ABCDEF',
10,
125.5
FROM Tally
SELECT s,i,d FROM @t
May 9, 2011 at 4:46 am
Both Paul and Nigel have good examples. I would like to point out something that Paul did in his code that I strongly recommend...
Different people built different base Tally tables. Some will build them starting at 0 and some will build them starting at 1. For simple things like expanding an insert as in this thread, harden your code to take advantage of the Tally Table no matter which one may be present by setting not only the end of the range for t.N, but also the beginning as "1".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2011 at 3:44 pm
Thanks for the help and to Jeff Moden for the great articles on tally tables! This is the code snippet I wound up using:
INSERT tblDupesDemo_ccb
SELECT 'ABCDEF', 10, 125.50
FROM dbo.tblTally_ccb
WHERE N >= 1 AND N <= 20
Prior to the snippet above I used GO X, e.g.,
INSERT tblDupesDemo_ccb VALUES ('ABCDEF',10,125.50)
GO 10
but that's a RBAR approach, and I'm striving for set-based solutions.
Thanks again.
Christian Bahnsen
May 9, 2011 at 5:17 pm
Thanks for the feedback, Christian.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply