Question Sql Server Data Copy Cheyali Excel

[ChampakDas]

I see what you got. Those get saved in CSV format. Ctrl A chesi..copy with format kotti paste that in excel.
[quote name='littlemoon' timestamp='1343834227' post='1302237706']

kani ala excel lo open chesthe format change aindi
[/quote]

[Desamudhuru]

[quote name='littlemoon' timestamp='1343831376' post='1302237444']
Hello,,,

Sql query nunchi data pull chesaka excel loki ela export cheyachu using SSMS?
[/quote]
thanks for the post. Naku edi teliyadu ni fost valla nerchukuna thanks.

[9Pardhu]

[color=#191970][font=Verdana, Arial, Helvetica][size=1][background=rgb(245, 245, 245)]Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel[/background][/size][/font][/color]

[color=#191970][font=Verdana, Arial, Helvetica][size=1][background=rgb(245, 245, 245)]Create an Excel file named testing having the headers same as that of table columns and use these queries[/background][/size][/font][/color]

[b]1 Export data to existing EXCEL file from SQL Server table[/b]

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable



[b]2 Export data from Excel to new SQL Server table[/b]

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')



[b]3 Export data from Excel to existing SQL Server table[/b]

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



[b]4 If you dont want to create an EXCEL file in advance and want to export data to it, use[/b]
EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

[color=#191970][font=Verdana, Arial, Helvetica][size=1][background=rgb(245, 245, 245)](Now you can find the file with data in tabular format)[/background][/size][/font][/color]


[b]5 To export data to new EXCEL file with heading(column names), create the following procedure[/b]
create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)


[color=#191970][font=Verdana, Arial, Helvetica][size=1][background=rgb(245, 245, 245)]After creating the procedure, execute it by supplying database name, table name and file path[/background][/size][/font][/color]


EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

[9Pardhu]

[color=blue][font=monospace][size=3]USE [/size][/font][/color][color=black][font=monospace][size=3][AdventureWorks][/size][/font][/color][color=gray][font=monospace][size=3];[/size][/font][/color]
[color=black][font=monospace][size=3]GO[/size][/font][/color]
[color=blue][font=monospace][size=3]INSERT INTO OPENROWSET [/size][/font][/color][color=gray][font=monospace][size=3]([/size][/font][/color][color=red][font=monospace][size=3]'Microsoft.Jet.OLEDB.4.0'[/size][/font][/color][color=gray][font=monospace][size=3], [/size][/font][/color][color=red][font=monospace][size=3]'Excel 8.0;Database=c:\contact.xls;'[/size][/font][/color][color=gray][font=monospace][size=3],[/size][/font][/color]
[color=red][font=monospace][size=3]'SELECT * FROM [Sheet1$]'[/size][/font][/color][color=gray][font=monospace][size=3])[/size][/font][/color]
[color=blue][font=monospace][size=3]SELECT TOP [/size][/font][/color][color=black][font=monospace][size=3]5 FirstName[/size][/font][/color][color=gray][font=monospace][size=3], [/size][/font][/color][color=black][font=monospace][size=3]LastName[/size][/font][/color]
[color=blue][font=monospace][size=3]FROM [/size][/font][/color][color=black][font=monospace][size=3]Person.Contact
GO[/size][/font][/color]

[Calm Guy]

Click on the Tools menu option in SSMS.
Click on Options->Query Results->Sql Server->Results to Grid
Tick "Include Column Headers when Copying or Saving the Results"

and then save the results in csv as champakdas suggested...