| Author |
Topic |
|
SqlGirl87
Starting Member
26 Posts |
 Posted - 2012-11-28 : 06:45:12
|
| I know this can be done with +"'" for each field, but I was wondering if there is a function to return a SQL result and wrap it in single quotes IF the result is NOT NULL?something like SELECT FUNCTION(*) from....If I have to manually do it for each column in the table, then ti is easier to export to excel and run a function there to wrap it in quotes and put a comma after it.Thanks |
|
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-28 : 07:11:52
|
| I know of no native feature in Transact SQL that would allow you generate the results as you have specified, except for the option you specified.However: you mentioned exporting to excel. If the purpose is to export to excel (or csv file or other types of export), and if you are using SSIS, you can instruct SSIS to add the single quotes.You also mentioned using Excel to wrap in single quotes and put a comma after it. If you are trying to generate a comma-separated string of the data in the selected columns, that is easily done using the XML capabilities of Transact SQL.Would you be able to post a sample of the data in your tables and a sample of the exact output that you are trying to generate from that sample data?________________________________________-- Yes, I am indeed a fictional character. |
 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-28 : 07:12:18
|
| For what reason you needed quoted values... I think you want export data to a CSV file.. right?If yes, Right-click on result set (in SSMS query results area) and then Click on Select All, then Save result as Now u can save results as CSV/Delimited/FixedLength format file--Chandu |
|
|
|
SqlGirl87
Starting Member
26 Posts |
Posted - 2012-11-28 : 07:34:32
|
quote:
Originally posted by bandi
For what reason you needed quoted values... I think you want export data to a CSV file.. right?If yes, Right-click on result set (in SSMS query results area) and then Click on Select All, then Save result as Now u can save results as CSV/Delimited/FixedLength format file--Chandu
I wanted to pull the results from an isolated DB formatted ready to insert into another onesimply need to add INSERT INTO .. Values () and drop the return line into the ()It is not possible in any way to copy or insert into the other table via SQL.But I can copy the data in txt format |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-28 : 07:56:34
|
http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table
Too old to Rock'n'Roll too young to die. |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-28 : 08:04:32
|
| These are steps to get double quoted comma separated result setSteps to follow:1)SELECT QUOTENAME(ColumnName1, '"'), QUOTENAME(ColumnNAME2, '"')FROM TableName2) Alt+T --> Options --> Query Results --> SQL Server--> Results to Text --> and then select Column Delimited in Output format --> OKYou have to restart ur SSMS after changes made--Chandu |
|
|
|
SqlGirl87
Starting Member
26 Posts |
Posted - 2012-11-28 : 08:57:04
|
quote:
Originally posted by bandi
These are steps to get double quoted comma separated result setSteps to follow:1)SELECT QUOTENAME(ColumnName1, '"'), QUOTENAME(ColumnNAME2, '"')FROM TableName2) Alt+T --> Options --> Query Results --> SQL Server--> Results to Text --> and then select Column Delimited in Output format --> OKYou have to restart ur SSMS after changes made--Chandu
Thanks but was aware of that, there are so many fields in one of the tables its easier to copy into excel and run a function there that will auto wrap them all in '',Was hoping you could perhaps to it around the * in the select instead of having to do each individual column - seems not but thanks for the response! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-28 : 09:20:30
|
quote:
Originally posted by webfred
http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table
Too old to Rock'n'Roll too young to die.
My above posted link leads to the following solution:This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008: 1.Right click on the database and go to Tasks -> Generate Scripts 2.Select the tables (or object) that you want to generate the script against. 3.Go to Set scripting options and click on the Advanced button. 4.In the General category, go to Type of data to script 5.There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK. You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.Is there something more I can do to get a reaction about this solution? Maybe to read it to you?
Too old to Rock'n'Roll too young to die. |
|
|
|
SqlGirl87
Starting Member
26 Posts |
Posted - 2012-11-28 : 15:49:03
|
quote:
Originally posted by webfred
quote:
Originally posted by webfred
http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table
Too old to Rock'n'Roll too young to die.
My above posted link leads to the following solution:This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008: 1.Right click on the database and go to Tasks -> Generate Scripts 2.Select the tables (or object) that you want to generate the script against. 3.Go to Set scripting options and click on the Advanced button. 4.In the General category, go to Type of data to script 5.There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK. You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.Is there something more I can do to get a reaction about this solution? Maybe to read it to you?
Too old to Rock'n'Roll too young to die.
Hey, thanks very much for that, very helpful - sorry missed your link earlier in the thread. |
|
|
|
|
Select result wrapped in single quotes?