January 26, 2011 at 9:41 am
Is it possible to insert several rows into a table, based upon a source table and not involving a cursor?
I've supplied this simple set-up to demonstrate what I'm after:
CREATE TABLE KeyTable(
KT_IDint
,KT_typeint
)
CREATE TABLE Tens_Table(
TT_IDint IDENTITY
,TT_Typeint
,TT_Messagevarchar(50)
)
CREATE TABLE Twenties_Table(
TW_IDint IDENTITY
,TW_Typeint
,TW_Messagevarchar(50)
);
GO
INSERT INTO KeyTable(KT_ID, KT_type)
SELECT 1, 10
UNION ALL
SELECT 2, 10
UNION ALL
SELECT 3, 20
UNION ALL
SELECT 4, 10
UNION ALL
SELECT 5, 20;
My expected results would be:
TT_ID TT_Type TT_Message
----------- ----------- --------------------------------------------------
1 10 1st Row Sourced from KeyTable - ID 1
2 10 2nd Row Sourced from KeyTable - ID 1
3 10 1st Row Sourced from KeyTable - ID 2
4 10 2nd Row Sourced from KeyTable - ID 2
5 10 1st Row Sourced from KeyTable - ID 4
6 10 2nd Row Sourced from KeyTable - ID 4
(6 row(s) affected)
TW_ID TW_Type TW_Message
----------- ----------- --------------------------------------------------
1 20 1st Row Sourced from KeyTable - ID 3
2 20 2nd Row Sourced from KeyTable - ID 3
3 20 1st Row Sourced from KeyTable - ID 5
4 20 2nd Row Sourced from KeyTable - ID 5
(4 row(s) affected)
This is a very simple adaption of what I'm currently working on, and what I am doing right now is using a cursor. It is correcting customer's data and will run once and doesn't involve that many records, so it isn't really a problem. It just bugs me.
What I really wanted was something along the lines of "For every Type of 10 in KeyTable insert a '1st Row' row into the Tens_table, then insert a '2nd Row' row into Tens_Table, with the originating ID. For every Type of 20 do the same, but to the Twenties Table".
It's probably something obvious, but I can' see it and have the feeling that it should be possible.
Can anybody satisfy my curiosity, and with something elegant?
Thanks,
BrainDonor.
January 26, 2011 at 10:20 am
This ought to get you started...
;WITH CTE AS (
SELECT KT_ID, KT_Type, RN = ROW_NUMBER() OVER (ORDER BY KT_ID)
FROM KeyTable
)
--INSERT INTO Tens_Table (TT_ID, TT_Type, TT_Message)
select KT_ID, KT_Type, CONVERT(varchar(5), RN) + ' <logic for st/nd/rd/etc>' + ' Row Sourced from KeyTable - ID ' + CONVERT(varchar(10), KT_ID)
from CTE
WHERE KT_Type = 10;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 26, 2011 at 11:57 am
CELKO (1/26/2011)
Thanks for posting DDL, but you did make a few mistakes in it. There were no keys and no way to have keys. You used IDENTITY, which is non-relational and cannot ever be a proper relational key. Learn the ISO-11179 rules about data element names, so you will stop putting "table" in table names.I also got a laugh at the leading commas in your code. We used to do that in the 1950's when we coded on punch cards; it made the deck of cards easier to re-use and rearrange. In the 1970's when we began doing research, this antique practice was one of several idioms that added 8-12% more time to maintaining code. In the 21-st century, we have been using "pretty printers" pm CRT screens to make the code look like English if possible; that means putting punctuation where it would be in a Latin alphabet language.
A type is what data modelers call an attribute property; the template for them is "<attribute>_type" which you have. But the look up tables for type encoding cannot have a magical, universal "id" -- think about how silly "blood_type_id" is as a data element name. This is like assigning an IDENTITY value to the mathematical constants pi, e, phi, etc,-- obviously wrong and silly. What we do is:
CREATE TABLE Key_Types
(kt_type INTEGER NOT NULL PRIMARY KEY,
kt_type_description VARCHAR(50) NOT NULL);
A good rule is that when two tables have the same structure, they model the same entity or relationship, but have been split on an attribute (Google "Attribute Splitting" as a design flaw) Would you have "Male_Personnel" and Female_Personnel" tables or just "Personnel" with a sex_code column? Again, this is obvious when you think about it.
Your two tables are that kind of mess. Perhaps, you want something like this?
CREATE TABLE TensTwenties -- needs a better name
(tt_id INTEGER NOT NULL PRIMARY KEY,
weird_10_20_flg INTEGER NOT NULL
CHECK (weird_10_20_flg IN (10, 10)),
tt_type INTEGER NOT NULL REFERENCES Key_Types (kt_type),
tt_message_txt VARCHAR(50) NOT NULL);
GO
Your discussion about first and second rows makes no sense in RDBMS; tables have no ordering. This is why you had to use cursors. This is a punch card system written in SQL and not anywhere near an RDBMS.
As for your original question, yes, muli-row (set) insertion is both easy and possible. You use a SELECT as the data source clause and set a search condition that returns one or more rows from a query.
INSERT INTO Foobar (..)
SELECT ..
FROM T1, T2, .., Tn
WHERE flob_seq IN (1,2));
But you need to have an RDBMS and not a deck of punch cards wearing a bad disguise 🙂
I think you're reading a bit much into the "leading commas". I use them after the term, as you would in written English, but I've heard good reasons for using them as leading punctuation instead of trailing. When they're at the front of the row instead of at the end, it becomes much easier to comment out that row and still run the procedure with no further modification. There are times I consider making the switch myself, but inertia and what is readable for me holds me back.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 26, 2011 at 6:05 pm
CELKO (1/26/2011)
When you said "comment out the line", that is what we did by physically pulling the card out of the deck. How about putting parentheses on separate lines (cards)?Another holdover from punch cards is that we only had 80 card columns, so we crammed as much as we could into a line (card) for the blocks of code we did not want to break up. Look at how many postings still have no spaces around math symbols and expressions. "2 + 2 = 4" is a lot easier to than "2+2=4" when the tersm get complicated.
Sure. I remember 80 columns, had to convert them as recently as 2002. Legacy systems abound and you might be stunned at what is still in use.
And yes, putting parenthesis on separate lines can also help when debugging.
I don't see this as a holdover from punch cards though, commenting out lines of code is a good way to assist in debugging.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 27, 2011 at 1:14 am
Ah, abuse. Completely unhelpful, pointless and altogether disappointing Celko. I have several excellent articles written by your good self that I still refer every now and again, but this kind of response is a complete waste of your talents.
I didn't post the DDL or data for the actual problem that I'm having because it is confidential, both to my employer and the customer's database where this little puzzle has been encountered. What I did post was an extremely simplified set of tables and data sufficient to get the concept across (and hopefully a solution). Nothing more.
Seriously, you're a bright lad capable of providing help and encouragement to a very wide audience, but this type of attitude wins you no fans. I'm old enough to go back to 6-bit mainframes, so this is "water off a duck's back" to coin a phrase. However, there are a lot of younger and more fragile egos out there seeking assistance that require a gentler hand, as well as useful advice.
BrainDonor.
January 27, 2011 at 1:39 am
WayneS (1/26/2011)
This ought to get you started...
Thanks, I think I know what you're getting at. I'll have a play with it later as time constraints mean I'm too close to completing it with the current method to re-work what I've already written.
I'll let you know how I get on with it.
BrainDonor.
January 28, 2011 at 10:14 am
BrainDonor (1/27/2011)
Ah, abuse. Completely unhelpful, pointless and altogether disappointing Celko. I have several excellent articles written by your good self that I still refer every now and again, but this kind of response is a complete waste of your talents.I didn't post the DDL or data for the actual problem that I'm having because it is confidential, both to my employer and the customer's database where this little puzzle has been encountered. What I did post was an extremely simplified set of tables and data sufficient to get the concept across (and hopefully a solution). Nothing more.
Seriously, you're a bright lad capable of providing help and encouragement to a very wide audience, but this type of attitude wins you no fans. I'm old enough to go back to 6-bit mainframes, so this is "water off a duck's back" to coin a phrase. However, there are a lot of younger and more fragile egos out there seeking assistance that require a gentler hand, as well as useful advice.
BrainDonor.
Ignore Joe Celko completely. He has no business being on SSC's forums.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2011 at 1:04 pm
Joe,
I came up in the ranks of PDP-11 assembly language. I've used just about every text editor (including TECO) that you can imagine. The whole purpose of formatting your code a certain way is to make it easier to eyeball and see typos and such.
I find that leading commas make SQL code much easier to develop (such as commenting out a line or deleting a line I don't want). That's just me. Someone else might find it easier to read with trailing commas.
There's no reason to ridicule someone for formatting code a certain way as long as it's readable.
Todd Fifield
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply