Using Macros - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Macros

Author

Topic

hellohello
Starting Member

12 Posts

Posted - 2011-05-31 : 09:50:43

Hi,

I want to dynamically update sql queries using a macro. I have to update information I have an Excel Sheet into my Sybase Database. How do I do this?? I could do it manually and go through every query 1 by 1 but there are over 2000 rows. Is there an easier way through using a macro??

Thank you in advance. (New job started last week so help would be greatly appreciated).

Regards T

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 09:59:58

You mean you want to update a sybase database from a spreadsheet?
Or are you extracting data from sybase into excel and want to output into sql server.

As a first option I create insert statements for a temp table (using an expression in the spreadsheet copied down for each row) in excel then copy them into a query window.

="insert #a select '" & SUBSTITUTE(A2,"'","''") & "','" & SUBSTITUTE(B2,"'","''") & "','" & SUBSTITUTE(C2,"'","''") & "','" & SUBSTITUTE(D2,"'","''") & "','" & SUBSTITUTE(E2,"'","''") & "','" & SUBSTITUTE(F2,"'","''") & "','" & SUBSTITUTE(G2,"'","''") & "','" & SUBSTITUTE(H2,"'","''") & "','" & SUBSTITUTE(I2,"'","''") & "','" & SUBSTITUTE(J2,"'","''") & "','" & SUBSTITUTE(K2,"'","''") & "','" & SUBSTITUTE(L2,"'","''") & "','" & SUBSTITUTE(M2,"'","''") & "'"

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hellohello
Starting Member

12 Posts

Posted - 2011-05-31 : 10:11:49

I want to update a sybase database from a spreadsheet using a macro. I have an update query.

update TABLE set ID = (select ID from TABLE where SYS_ID = 1 and SUBSYS = 1 ),
where ID = 2

but there are over 2000 of these queries if i was to manually go through the spreadsheet and pick out the numbers. any way of using a macro or something to dynamically update the tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources