April 20, 2011 at 6:00 am
Grant Fritchey (4/20/2011)
I'm sure it's the same in other countries, but you can get large quantities of data from the US Census bureau. Just keep poking around on the site. It's scattered all over the place.
All right, thanks for the tip!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2011 at 6:11 am
Brandie Tarvin (4/19/2011)
I'm going to violate the rule and add a "technical" question to The Thread under the caveat that I don't really need an answer for a problem so much as I'm just curious...At my workplace, we often have a need to manually INSERT hard data from one environment to the next. Say I have Table1 on my Dev database. I want to take some or all of that data and insert it into Table1 in Test. Now, I can't use the Import / Export wizard because this insertion is part of our SDLC. So, while I could create an SSIS package (or use the wizard to do it), sometimes it's just easier, especially with small tables, to force the data into an INSERT T-SQL statement.
Some people are fond of doing this as multiple single INSERT statements (INSERT ... SELECT ...; INSERT .... SELECT ...;). Me, I like using 1 INSERT clause and then a SELECT .... UNION ALL SELECT ... type of format.
If you had to choose between the two T-SQL options, which would you choose and why?
Would depend on the server and the data.
If it were a busy server and the insert were a lot of rows, I'd break it down, to avoid long locks. Otherwise, I tend towards the single-statement version, to make it into a single ACID statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2011 at 6:32 am
bitbucket-25253 (4/19/2011)
opc.three
The SSMS Tools Pack rocks! and so do RAR files
Care to share the link for the Tools Pack?
Move back to Lutz's post if you haven't already.
Greg E
April 20, 2011 at 6:41 am
Brandie Tarvin (4/20/2011)
WayneS (4/19/2011)
Greg Edwards-268690 (4/19/2011)
GSquared (4/19/2011)
Lynn Pettis (4/19/2011)
And once again Celko sticks his nose where it really wasn't needed. The OPs question had already been answered.And Joe's answer, while demanding that others follow published standards, was itself a gross violation of data architectural standards. Though I have to admit, I'm getting tired of calling him on his hypocrisy.
Reminder to self - don't feed the Gus. It bites back.
Greg E
Couldn't resist replying to Gus' excellent reply on that thread...
Wayne, I like you, but I am severely tempted to report your reply and get it removed. Your comment is completely non helpful, non constructive, and just plain mean-spirited. If that's what you meant to achieve with your reply, it's working. If that's not how you meant it to come across, then you need to revisit what you said.
How would you feel if someone responded to one of your posts with those exact words?
EDIT: We should all remember that some employers actually do use GOOGLE to research potential employees. Do we really want a future boss to see us acting like nasty children on a professional forum?
Brandie, thanks for calling me on this. You're absolutely right.
(Now, I just need to get Gus to remove my message where he quoted it...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2011 at 7:14 am
WayneS (4/20/2011)
Brandie, thanks for calling me on this. You're absolutely right.
You were probably acting like your avatar when you were reading Celko's post 😀 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2011 at 7:22 am
WayneS (4/20/2011)
Brandie Tarvin (4/20/2011)
WayneS (4/19/2011)
Greg Edwards-268690 (4/19/2011)
GSquared (4/19/2011)
Lynn Pettis (4/19/2011)
And once again Celko sticks his nose where it really wasn't needed. The OPs question had already been answered.And Joe's answer, while demanding that others follow published standards, was itself a gross violation of data architectural standards. Though I have to admit, I'm getting tired of calling him on his hypocrisy.
Reminder to self - don't feed the Gus. It bites back.
Greg E
Couldn't resist replying to Gus' excellent reply on that thread...
Wayne, I like you, but I am severely tempted to report your reply and get it removed. Your comment is completely non helpful, non constructive, and just plain mean-spirited. If that's what you meant to achieve with your reply, it's working. If that's not how you meant it to come across, then you need to revisit what you said.
How would you feel if someone responded to one of your posts with those exact words?
EDIT: We should all remember that some employers actually do use GOOGLE to research potential employees. Do we really want a future boss to see us acting like nasty children on a professional forum?
Brandie, thanks for calling me on this. You're absolutely right.
(Now, I just need to get Gus to remove my message where he quoted it...)
Compared to what Celko dishes out, I don't believe Wayne's statement was out of line, at all. But I do agree that we shouldn't allow him to drag us down to his level.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2011 at 7:38 am
Brandie Tarvin (4/20/2011)
LutzM (4/19/2011)
Brandie Tarvin (4/19/2011)
I'm going to violate the rule and add a "technical" question to The Thread under the caveat that I don't really need an answer for a problem so much as I'm just curious...At my workplace, we often have a need to manually INSERT hard data from one environment to the next. Say I have Table1 on my Dev database. I want to take some or all of that data and insert it into Table1 in Test. Now, I can't use the Import / Export wizard because this insertion is part of our SDLC. So, while I could create an SSIS package (or use the wizard to do it), sometimes it's just easier, especially with small tables, to force the data into an INSERT T-SQL statement.
Some people are fond of doing this as multiple single INSERT statements (INSERT ... SELECT ...; INSERT .... SELECT ...;). Me, I like using 1 INSERT clause and then a SELECT .... UNION ALL SELECT ... type of format.
If you had to choose between the two T-SQL options, which would you choose and why?
I would right click on the table, choose SSMS Tools -> Generate Insert Statements... and apply the WHERE clause and/or the number of rows to return :-D:-D:-D
I've never seen SSMS Tools before. Thanks for the reference, Lutz.
All, yeah, the row count when I do it isn't much more than 25 to 30 usually. On rare occasions, it might be up to 200 rows (reasons why I don't want to use SSIS). And I'm working in 2005.
Thanks for the input. This has been an interesting discussion.
Do you already use SSIS and just don't want to use it for this small task?
--------------------------------------
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
April 20, 2011 at 7:43 am
Stefan Krzywicki (4/20/2011)
Brandie Tarvin (4/20/2011)
LutzM (4/19/2011)
Brandie Tarvin (4/19/2011)
I'm going to violate the rule and add a "technical" question to The Thread under the caveat that I don't really need an answer for a problem so much as I'm just curious...At my workplace, we often have a need to manually INSERT hard data from one environment to the next. Say I have Table1 on my Dev database. I want to take some or all of that data and insert it into Table1 in Test. Now, I can't use the Import / Export wizard because this insertion is part of our SDLC. So, while I could create an SSIS package (or use the wizard to do it), sometimes it's just easier, especially with small tables, to force the data into an INSERT T-SQL statement.
Some people are fond of doing this as multiple single INSERT statements (INSERT ... SELECT ...; INSERT .... SELECT ...;). Me, I like using 1 INSERT clause and then a SELECT .... UNION ALL SELECT ... type of format.
If you had to choose between the two T-SQL options, which would you choose and why?
I would right click on the table, choose SSMS Tools -> Generate Insert Statements... and apply the WHERE clause and/or the number of rows to return :-D:-D:-D
I've never seen SSMS Tools before. Thanks for the reference, Lutz.
All, yeah, the row count when I do it isn't much more than 25 to 30 usually. On rare occasions, it might be up to 200 rows (reasons why I don't want to use SSIS). And I'm working in 2005.
Thanks for the input. This has been an interesting discussion.
Do you already use SSIS and just don't want to use it for this small task?
Exactly.
I can write a dynamic INSERT statement based off of table data faster than I can create the SSIS package. Using the Wizard to create the package doesn't work either, because I still have to alter the package for our release process (add a config file, etc.).
April 20, 2011 at 7:49 am
Brandie Tarvin (4/20/2011)
Stefan Krzywicki (4/20/2011)
Brandie Tarvin (4/20/2011)
LutzM (4/19/2011)
Brandie Tarvin (4/19/2011)
I'm going to violate the rule and add a "technical" question to The Thread under the caveat that I don't really need an answer for a problem so much as I'm just curious...At my workplace, we often have a need to manually INSERT hard data from one environment to the next. Say I have Table1 on my Dev database. I want to take some or all of that data and insert it into Table1 in Test. Now, I can't use the Import / Export wizard because this insertion is part of our SDLC. So, while I could create an SSIS package (or use the wizard to do it), sometimes it's just easier, especially with small tables, to force the data into an INSERT T-SQL statement.
Some people are fond of doing this as multiple single INSERT statements (INSERT ... SELECT ...; INSERT .... SELECT ...;). Me, I like using 1 INSERT clause and then a SELECT .... UNION ALL SELECT ... type of format.
If you had to choose between the two T-SQL options, which would you choose and why?
I would right click on the table, choose SSMS Tools -> Generate Insert Statements... and apply the WHERE clause and/or the number of rows to return :-D:-D:-D
I've never seen SSMS Tools before. Thanks for the reference, Lutz.
All, yeah, the row count when I do it isn't much more than 25 to 30 usually. On rare occasions, it might be up to 200 rows (reasons why I don't want to use SSIS). And I'm working in 2005.
Thanks for the input. This has been an interesting discussion.
Do you already use SSIS and just don't want to use it for this small task?
Exactly.
I can write a dynamic INSERT statement based off of table data faster than I can create the SSIS package. Using the Wizard to create the package doesn't work either, because I still have to alter the package for our release process (add a config file, etc.).
Sure, makes sense I just wasn't sure if you'd used SSIS yet. I've worked witih people who just didn't want to have to learn a new tool so they avoided it whenever they could. I was going to suggest that if you hadn't used it at all you might want to use it for this just to get it on your resume.
--------------------------------------
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
April 20, 2011 at 7:54 am
I went to the latest SQL Server Saturday in Boston and had a great time. I'm mentioning it now because I've been using a little trick I learned in one of the sessions and it reminds me how valuable these events are. Sure I learned a lot of theory and concepts, but day to day I find myself using this little SSMS throwaway tip.
Under your table name, click on the Columns folder and drag it to your query window and you get a complete list of all your columns. A time saver and makes me less likely to use * even in quick queries I don't intend to use more than once.
Sure, it seems a ridiculous, little thing to rave about, but when you learn something you use every day, that changes the way you code, you find yourself thanking the person that you learned it from every day.
SQL Server Saturdays are awesome and I'd never have known about them if it wasn't for SQLServerCentral.com
--------------------------------------
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
April 20, 2011 at 8:29 am
Koen Verbeeck (4/20/2011)
WayneS (4/20/2011)
Brandie, thanks for calling me on this. You're absolutely right.You were probably acting like your avatar when you were reading Celko's post 😀 😉
Well, yeah, but it's not a justifiable excuse for posting what I did.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2011 at 9:01 am
Stefan Krzywicki (4/20/2011)
I went to the latest SQL Server Saturday in Boston and had a great time. I'm mentioning it now because I've been using a little trick I learned in one of the sessions and it reminds me how valuable these events are. Sure I learned a lot of theory and concepts, but day to day I find myself using this little SSMS throwaway tip.Under your table name, click on the Columns folder and drag it to your query window and you get a complete list of all your columns. A time saver and makes me less likely to use * even in quick queries I don't intend to use more than once.
Sure, it seems a ridiculous, little thing to rave about, but when you learn something you use every day, that changes the way you code, you find yourself thanking the person that you learned it from every day.
SQL Server Saturdays are awesome and I'd never have known about them if it wasn't for SQLServerCentral.com
Thanks for sharing that. While we are on the topic I figured I would share too 🙂
The drag-and-drop feature you mentioned between the Object Explorer and a Query Window works on all proc and function Parameter tree items as well.
I don't get paid from RedGate, but I have been using SQL Prompt for years and they have it figured out. If I type SELECT * FROM dbo.Table; and then put my cursor immediately after the * and hit Tab it expands the * to the set of column names from dbo.Table. The expansion works on queries with JOINs too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 9:15 am
opc.three (4/20/2011)
Stefan Krzywicki (4/20/2011)
I went to the latest SQL Server Saturday in Boston and had a great time. I'm mentioning it now because I've been using a little trick I learned in one of the sessions and it reminds me how valuable these events are. Sure I learned a lot of theory and concepts, but day to day I find myself using this little SSMS throwaway tip.Under your table name, click on the Columns folder and drag it to your query window and you get a complete list of all your columns. A time saver and makes me less likely to use * even in quick queries I don't intend to use more than once.
Sure, it seems a ridiculous, little thing to rave about, but when you learn something you use every day, that changes the way you code, you find yourself thanking the person that you learned it from every day.
SQL Server Saturdays are awesome and I'd never have known about them if it wasn't for SQLServerCentral.com
Thanks for sharing that. While we are on the topic I figured I would share too 🙂
The drag-and-drop feature you mentioned between the Object Explorer and a Query Window works on all proc and function Parameter tree items as well.
I don't get paid from RedGate, but I have been using SQL Prompt for years and they have it figured out. If I type SELECT * FROM dbo.Table; and then put my cursor immediately after the * and hit Tab it expands the * to the set of column names from dbo.Table. The expansion works on queries with JOINs too.
You don't get paid by RedGate, but I hope you're at least getting kick-backs from Grant! : -)
--------------------------------------
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
April 20, 2011 at 10:01 am
Stefan Krzywicki (4/20/2011)
opc.three (4/20/2011)
Stefan Krzywicki (4/20/2011)
I went to the latest SQL Server Saturday in Boston and had a great time. I'm mentioning it now because I've been using a little trick I learned in one of the sessions and it reminds me how valuable these events are. Sure I learned a lot of theory and concepts, but day to day I find myself using this little SSMS throwaway tip.Under your table name, click on the Columns folder and drag it to your query window and you get a complete list of all your columns. A time saver and makes me less likely to use * even in quick queries I don't intend to use more than once.
Sure, it seems a ridiculous, little thing to rave about, but when you learn something you use every day, that changes the way you code, you find yourself thanking the person that you learned it from every day.
SQL Server Saturdays are awesome and I'd never have known about them if it wasn't for SQLServerCentral.com
Thanks for sharing that. While we are on the topic I figured I would share too 🙂
The drag-and-drop feature you mentioned between the Object Explorer and a Query Window works on all proc and function Parameter tree items as well.
I don't get paid from RedGate, but I have been using SQL Prompt for years and they have it figured out. If I type SELECT * FROM dbo.Table; and then put my cursor immediately after the * and hit Tab it expands the * to the set of column names from dbo.Table. The expansion works on queries with JOINs too.
You don't get paid by RedGate, but I hope you're at least getting kick-backs from Grant! : -)
I can't comment on the kick-back program as it's currently configured. Suffice to say, if you've received a check, you earned it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 25,846 through 25,860 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply