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
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 30

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/20/2013 :  14:41:04  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi

IF @GO=1
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END

Msg 2714, Level 16, State 1, Procedure ParseXmlPersonInfo, Line 155
There is already an object named '# WS_DEPTS1' in the database.



kmkmmm



If the code is inside any loop then

Create table #WS_DEPTS1 (colName datatype)
While Loop with some logic
Begin
...
...

IF @GO=1
BEGIN
Insert into #WS_DEPTS1 (colname)
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
Insert into #WS_DEPTS1 (colname)
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END
....
...
ENd





Cheers
MIK
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/20/2013 :  09:39:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by MIK_2008

quote:
Originally posted by pascal_jimi

IF @GO=1
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END

Msg 2714, Level 16, State 1, Procedure ParseXmlPersonInfo, Line 155
There is already an object named '# WS_DEPTS1' in the database.



kmkmmm



If the code is inside any loop then

Create table #WS_DEPTS1 (colName datatype)
While Loop with some logic
Begin
...
...

IF @GO=1
BEGIN
Insert into #WS_DEPTS1 (colname)
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
Insert into #WS_DEPTS1 (colname)
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END
....
...
ENd





Cheers
MIK


You can also use dynamic sql like this http://beyondrelational.com/modules/2/blogs/70/posts/10926/createalter-procedure-in-a-single-batch.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Pamelag52
Starting Member

USA
1 Posts

Posted - 02/26/2014 :  10:47:11  Show Profile  Reply with Quote
I am importing data from an Excel spreadsheet into a SQL table using Microsoft.ACE.OLEDB.12.0. Everything is working fine except the zip code field/column is being translated as a float instead of character field when I insert this data into another table This makes the zip code show as '5.60033e+008' instead of '560032658'. When I issue the command below, the zip code shows as expected. But when I insert the results into another table, it converts it to a float even though the field it is being inserted into is varchar. Any ideas?

SELECT * into TempPayments FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=f:\FranklinPayments\2013\210_130731_130806.xlsx;Extended Properties=Excel 12.0')...[PAYMENT_DATA$]
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2014 :  08:20:26  Show Profile  Reply with Quote
suspecting that you might need to adjust the field type in excel. Have you check by making the excel field number/text so that it appears correctly and use same datatype in sql table too.

Cheers
MIK
Go to Top of Page

magozeta
Starting Member

2 Posts

Posted - 04/28/2014 :  10:02:42  Show Profile  Reply with Quote
Hello!
Many Thanks for your work!
I used this script:
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\testing.xlsx;',
'SELECT Code,Name,Cost,Quantity FROM [SheetName$]') select Code,Name,Cost,Quantity from SQLServerTable

to insert a query result to excel file "Template.xlsx" created previously.

My file "Template.xlsx" has 5 columns("Code" A1, "Name" A2, "Cost" A3, "Quantity" A4, "Total" =A3*A4).

in the first row there are headings and in the second row(cell Total) there is the formula.

How can I duplicate this row for 'n' records? sql query doesn't calculate the multiplication!

thanks!

Edited by - magozeta on 04/28/2014 10:03:25
Go to Top of Page

truongtung_90
Starting Member

Vietnam
1 Posts

Posted - 05/09/2014 :  01:07:41  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

But Excel has a latency problem with this. You have to enter the cell in Excel and press enter. I think this has to do with OpenRowSet exports the formula as text.


Peter Larsson
Helsingborg, Sweden


quote:
Originally posted by madhivanan

Yes it seems though the cell is formated as Number the data are of varchar type until the cell is focussed and Press Enter

Madhivanan

Failing to plan is Planning to fail


I have a SQL database which connect to WINCC (Industry Control Software), my SQL database table is alway auto update real time. I created Excel sheet and exported data to excel from SQL database, but i had a problem,althout SQL database update real time but Excel table was not auto and real time update. How do i do it.Someone help me, please! (sorry because my english is very bad)

Edited by - truongtung_90 on 05/09/2014 04:14:35
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 06/17/2014 :  02:12:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by truongtung_90

quote:
Originally posted by SwePeso

I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

But Excel has a latency problem with this. You have to enter the cell in Excel and press enter. I think this has to do with OpenRowSet exports the formula as text.


Peter Larsson
Helsingborg, Sweden


quote:
Originally posted by madhivanan

Yes it seems though the cell is formated as Number the data are of varchar type until the cell is focussed and Press Enter

Madhivanan

Failing to plan is Planning to fail


I have a SQL database which connect to WINCC (Industry Control Software), my SQL database table is alway auto update real time. I created Excel sheet and exported data to excel from SQL database, but i had a problem,althout SQL database update real time but Excel table was not auto and real time update. How do i do it.Someone help me, please! (sorry because my english is very bad)


See if this helps http://superuser.com/questions/254526/how-can-i-make-excel-fetch-data-from-a-database-automatically-when-i-open-the-sp

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page
 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.11 seconds. Powered By: Snitz Forums 2000