SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select result wrapped in single quotes?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SqlGirl87
Starting Member

26 Posts

Posted - 11/28/2012 :  06:45:12  Show Profile  Reply with Quote
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

United Kingdom
39 Posts

Posted - 11/28/2012 :  07:11:52  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  07:12:18  Show Profile  Reply with Quote
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
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/28/2012 :  07:34:32  Show Profile  Reply with Quote
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 one

simply 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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/28/2012 :  07:56:34  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  08:04:32  Show Profile  Reply with Quote
These are steps to get double quoted comma separated result set

Steps to follow:
1)SELECT QUOTENAME(ColumnName1, '"'), QUOTENAME(ColumnNAME2, '"')
FROM TableName
2) Alt+T --> Options --> Query Results --> SQL Server--> Results to Text --> and then select Column Delimited in Output format --> OK

You have to restart ur SSMS after changes made

--
Chandu
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/28/2012 :  08:57:04  Show Profile  Reply with Quote
quote:
Originally posted by bandi

These are steps to get double quoted comma separated result set

Steps to follow:
1)SELECT QUOTENAME(ColumnName1, '"'), QUOTENAME(ColumnNAME2, '"')
FROM TableName
2) Alt+T --> Options --> Query Results --> SQL Server--> Results to Text --> and then select Column Delimited in Output format --> OK

You 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!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/28/2012 :  09:20:30  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/28/2012 :  15:49:03  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000