March 30, 2015 at 7:05 pm
I have a list of paired row IDs in a tempTable, e.g.
ID / MatchID
1 / 8031
2 / 8032
3 / 8034
I need to do an insert select where I take a table, and every where there is a record (1 to many) that has an ID, I need to create a matching record for the MatchID.
I assume it is something like:
INSERT INTO myTable
(ID,someData,someData2)
SELECT tempTable.matchID as ID, myTable someData, myTable.someData2
FROM myTable, tempTable WHERE myTable.ID = tempTable.ID
or do I need to do a Join in the select? This is production data and I'd hate to mess this up because I'm 13 hours into this day already and not thinking quite clearly anymore.
Thanks for any help.
March 30, 2015 at 7:22 pm
Here is the actual T-SQL I think this will work:
INSERT INTO [notes]
([projectID]
,[userName]
,[createdDate]
,
,[emailed]
,[attachment])
SELECT [noteID]
, T.matchID as [projectID]
,[userName]
,[createdDate]
,
,[emailed]
,[attachment]
FROM [notes] N, [tempIDs] T WHERE N.projectID = T.projectID
March 31, 2015 at 10:58 am
As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not "DATETIME", etc.. This applies not only on case-sensitive servers but also under specific conditions even on case-insensitive servers, thus it's safest to use lowercase every time.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 31, 2015 at 12:27 pm
ScottPletcher (3/31/2015)
As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not "DATETIME", etc.. This applies not only on case-sensitive servers but also under specific conditions even on case-insensitive servers, thus it's safest to use lowercase every time.
That is very interesting, can you elaborate further on this?
😎
March 31, 2015 at 12:32 pm
A JOIN is preferred over the older-style WHERE clause, although with an INNER JOIN they should function the same either way:
INSERT INTO myTable
(ID,someData,someData2)
SELECT
tempTable.matchID as ID, myTable.someData, myTable.someData2
FROM myTable
INNER JOIN tempTable ON myTable.ID = tempTable.ID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 31, 2015 at 1:30 pm
Eirikur Eiriksson (3/31/2015)
ScottPletcher (3/31/2015)
As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not "DATETIME", etc.. This applies not only on case-sensitive servers but also under specific conditions even on case-insensitive servers, thus it's safest to use lowercase every time.That is very interesting, can you elaborate further on this?
😎
Ditto. Very interesting.
-- Itzik Ben-Gan 2001
March 31, 2015 at 4:02 pm
CELKO (3/31/2015)
use lowercase only for data type names in SQL Server
.
No. The human eye reads certain words as a single unit called a bouma. The keywords in programming languages should be in UPPERCASE. This was worth an 8-12% improvement in maintenance time when I was doing the research at AIRMICS in my misspent youth in research.
We've been through all this. Lower case can cause ABENDS. You never answered my q in response on the other forum either:
Do you want everyone to accept periodic, unexpected ABENDS -- on code that other times works perfectly -- to satisfy "bouma" or not??
http://www.sqlservercentral.com/Forums/Topic1623636-392-2.aspx
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 31, 2015 at 5:29 pm
ScottPletcher (3/31/2015)
As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not "DATETIME", etc.. This applies not only on case-sensitive servers but also under specific conditions even on case-insensitive servers, thus it's safest to use lowercase every time.
I've never seen it be true on a case-insensitive server. Do you have an example of such a thing, Scott? I ask because our standard is all uppercase in this area.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2015 at 5:55 pm
ScottPletcher (3/31/2015)
CELKO (3/31/2015)
use lowercase only for data type names in SQL Server
.
No. The human eye reads certain words as a single unit called a bouma. The keywords in programming languages should be in UPPERCASE. This was worth an 8-12% improvement in maintenance time when I was doing the research at AIRMICS in my misspent youth in research.
We've been through all this. Lower case can cause ABENDS. You never answered my q in response on the other forum either:
Do you want everyone to accept periodic, unexpected ABENDS -- on code that other times works perfectly -- to satisfy "bouma" or not??
http://www.sqlservercentral.com/Forums/Topic1623636-392-2.aspx%5B/quote%5D
Abend? Jeez, now there's a word I haven't heard since I was using IBM 360 JCL!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 31, 2015 at 6:52 pm
CELKO (3/31/2015)
Abend? Jeez, now there's a word I haven't heard since I was using IBM 360 JCL!
LOL! Remember the quote: "OS/JCL makes sense after you have studied it for a Year " from our day?
I have never had an ABENDS in Microsoft. Is this a problem?
Actually OS/JCL was one of my fortes back in the day.
Tell me you remember the blue-covered book by Gary Deward Brown (known to be the bible of OS/JCL).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 1, 2015 at 8:55 am
I've seen it cause errors, although it's somewhat obscure, a combination of things, including linked servers, IIRC. Perhaps it's been fixed, but I don't intend to risk job failures just because of that.
Celko previously acknowledged, in the q I linked to above, that MS was trying to fix the problem with uppercase type names. Is he saying that the problem is 100% corrected?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 1, 2015 at 11:58 am
dwain.c (3/31/2015)
CELKO (3/31/2015)
Abend? Jeez, now there's a word I haven't heard since I was using IBM 360 JCL!
LOL! Remember the quote: "OS/JCL makes sense after you have studied it for a Year " from our day?
I have never had an ABENDS in Microsoft. Is this a problem?
Actually OS/JCL was one of my fortes back in the day.
Tell me you remember the blue-covered book by Gary Deward Brown (known to be the bible of OS/JCL).
Here is something for a true retro feel[/url]
😎
April 6, 2015 at 5:56 pm
CELKO (4/2/2015)
Tell me you remember the blue-covered book by Gary Deward Brown (known to be the bible of OS/JCL).
how about the McCracken books? His A Guide to Fortran Programming , A Guide to COBOL Programming and A Guide to Algol Programming , ertc. were the standards texts forever.
Oh Ghod! We are old!
Thanks for the memories Joe. Thankfully I never had to program in Cobol or Algol, but I did teach Fortran. Magnificent language for its time. Remember how you used to have to (before Fortran 77) load character data into REAL/INTEGER variables and then access them through EQUIVALENCE statements? I wonder what the "strong-typing" advocates would say about that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 7, 2015 at 9:54 am
CELKO (3/30/2015)
>> I have a list of paired row IDs in a Fake_Tape_1, e.g. <<I hope not! Lists are not part of SQL or RDBMS. Rows are identified by a key, not a physical row_id. Putting data in a temp table is how COBOL programmers fake a scratch tape, not how to program in SQL. Camelcase does not work; the eye jumps to the upperCase letters then back to the start.
You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
And you need to read and download the PDF for:
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/
Here is my guess:
CREATE TABLE Something_Pairs
(foo_id INTEGER NOT NULL UNIQUE,
bar_id INTEGER NOT NULL UNIQUE);
>> I need to do an INSERT SELECT [sic: this is just an insertion statement] where I take a table, and everywhere there is a record [sic: rows are not anything like records] (1 to many) that has an foo_id, I need to create a matching record [sic] for the bar_id. <<
Where is this 1:m relationship? What does “matching record” mean? Since the goal of any database (not just SQL) is to reduce redundancy why are you increasing it?
If you are trying to do a translation from one encoding to another, put this in a VIEW instead of thinking in pure physical solutions to mimic mag tapes.
I needed to quick and dirty duplicate all the data for a single company in our system so we could film some tutorials. I needed actual data, but changed names and specifics so that I wasn't exposing private information in a tutorial. Thanks for the input though.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply