SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Insertion into Access DB using T-SQL


Data Insertion into Access DB using T-SQL

Author
Message
lraju10
lraju10
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 197
Hi Guys,

I am trying to insert data from SQLServer into an access database,using T-SQL

I used OPENROWSET to Select the data from access into SQLServer, it worked fine

QUERY:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','"ServerName"';'admin';'',TableName);

My table has columns with the changing month names

is there any way, i could do some sort of SELECT INTO an access DB using T-SQL ..?


Note : This is an automated report, i cannot use Import Export Wizard, there is no SSIS installed on the machine
Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 1099
What about using ODBC and creating a linked table in Access so you have constant and immediate access to the data for the report?
lraju10
lraju10
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 197
Hi Erin, Client requirement is to have a report in push off a button.. using ODBC Linked Tables is more of a manual process.. let me know your thoughts on this.....
Uwe Ricken
Uwe Ricken
SSC Eights!
SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)SSC Eights! (901 reputation)

Group: General Forum Members
Points: 901 Visits: 878
Hallo Iraju10,

this is not possible. To push data into ACCESS with OPENROWSET.
Basically the only possible solution is an access db and start of it by a SQL Server JOB.
In the access db you have an autoexec macro which may pull the data into local tables.

Another option maybe BCP but I'm not sure whether it supports mdb as OUTPUT-Format.
Last but not least think about an SSIS package - that's exactly for solutions you are looking for.

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 1099
Another solution could be, depending on the design of the report, to put it in Excel and have a linked datasource that updates on open so that every time someone opens the report the current numbers are available. I've done book-keeping reports for clients that way.
lraju10
lraju10
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 197
we thought about implementing this report in SSIS, but we are dealing with 24 months worth of sales data, as well quarterly and yearly data, i have dynamically changing columns

For Example : for the Month for August 2012, my table columns names will be

tblSalesData : SalesId, Name, Address, Zip,[Aug'12],[Jul'12],[Jun'12],[May'12]...... [Sep'10],[12 Months Ended Aug'12], [Q2'12],[Q1'12],[Q4'11].....[Q4'10]

for the Month of September 2012, my table columns names will be
tblSalesData : SalesId, Name, Address, Zip,[Aug'12],[Jul'12],[Jun'12],[May'12]...... [Sep'10],[12 Months Ended Sep'12],[ Q3'12] [Q2'12],[Q1'12],[Q4'11].....[Q4'10] , i have built this column names using dynamic sql


In SSIS, is there a way to map this changing column names in Data Flow Task ...?

may i know your thoughts on this....? appreciate your help..

Note: No Partial Quarters are allowed, so for August we start from [Q2'12], where as September we start from [Q3'12]
lraju10
lraju10
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 197
Hey Erin,

this is an access report, where the client has the front GUI, i am populating the back end data tables

is there way, i could populate my data into excel and feed the access tables ....?

Thanks for your help...?
Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 1099
Sure you could do it that way. Open your excel and have it pull down the table into a worksheet and then have that worksheep mapped to a file table in access. Not sure how that's going to work with dynamic column names but it's certainly doable.
Don Urquhart
Don Urquhart
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 1969
Do you really need the data in Access?

If not, you could setup a linked table(s) to the source data in SQL and design the report on those tables.


If you don't stand for something, you'll fall for anything!,

Don Urquhart
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search