April 19, 2011 at 2:15 pm
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
April 19, 2011 at 3:00 pm
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
April 19, 2011 at 3:29 pm
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
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 3:38 pm
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...
Now you guys have gone and done it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2011 at 4:34 pm
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
The SSMS Tools Pack rocks! and so do RAR files 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 7:39 pm
opc.three
The SSMS Tools Pack rocks! and so do RAR files
Care to share the link for the Tools Pack?
April 19, 2011 at 7:42 pm
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?
Not a direct link but I have it linked in this article[/url] 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 1:30 am
Craig Farrell (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?
If I'm automating, I use the seperate insert for each, even if it may not be as optimal. I've had UNION ALL's choke at around 2000 records.
For quick & dirty spreadsheet imports I use the UNION ALL format because it's ... well... quick and easy. Like Craig I've had it choke but on more like 10k rows.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 1:45 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?
If the generated TSQL statements are to much to handle ...
There is still good old BCP available.
(I've had a vendor ship me a 1GB .SQL file containing all single row insert statements.
They were even so lazy to not put it in a single (or a number of) transactions)
[edited] code removed to respect The Code for The Thread :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 4:59 am
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.
April 20, 2011 at 5:07 am
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?
April 20, 2011 at 5:42 am
I was asked to give a PowerPivot demo in the not so distant future.
I haven't really used it before (but I'm the only BI guy available in the office), so I'm looking for ideas on datasets to use in the demo.
Any suggestions for (very) large datasets, that are publicly available?
I'm thinking about stock exchange, weather forecasts et cetera. Is there a place to download such things?
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 5:59 am
Koen Verbeeck (4/20/2011)
I was asked to give a PowerPivot demo in the not so distant future.I haven't really used it before (but I'm the only BI guy available in the office), so I'm looking for ideas on datasets to use in the demo.
Any suggestions for (very) large datasets, that are publicly available?
I'm thinking about stock exchange, weather forecasts et cetera. Is there a place to download such things?
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.
"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
April 20, 2011 at 5:59 am
OK, after I asked the question here, I found the following list after some Google-fu:
http://www.powerpivot-info.com/post/50-list-suggested-datasets-to-test-powerpivot
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:00 am
Koen Verbeeck (4/20/2011)
I was asked to give a PowerPivot demo in the not so distant future.I haven't really used it before (but I'm the only BI guy available in the office), so I'm looking for ideas on datasets to use in the demo.
Any suggestions for (very) large datasets, that are publicly available?
I'm thinking about stock exchange, weather forecasts et cetera. Is there a place to download such things?
The NHTSA (National Highway Transportation Safety Administration) has large files for car recalls and technical service bulletins.
http://www-odi.nhtsa.dot.gov/recalls/[/url]
The US Treasury also allows downloads of different bond rates, etc.
http://www.federalreserve.gov/econresdata/releases/statisticsdata.htm
Does that help?
Also check NOAH if you're interested in weather related items. They may have data downloads you can grab.
Viewing 15 posts - 25,831 through 25,845 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply