Read from one database, write to another

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

Read from one database, write to another

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-02-28 : 17:20:21

I have been working within one database...now I would like to read and process data from one database, and write it into another... I am use sql server management studio.

the code below sets a beginning date(1/7/20078); figures out the weekly period, selects and counts records for that weekly period; then loops to the next week...I do 60 weeks...

so in the results window I have 60 independent tables..one for each weekly period...I was wanting to write the result of each week into an update record in a table..in a differnt database.

From table il084r5.arsystem.dbo.chg_change
to table il084s4.scorecard.dbo.importfromremedy

oh, and I read-only rights to the 'from' table...

*************************
dont know if the code really matters...but here it is...

declare @weekstart datetime,@weekend datetime, @OnThisDay datetime, @loop int
set @OnThisDay = '1/7/2008'

set @loop = 60

While @loop >0
begin
set @weekstart = dateadd(day,datediff(day,0,@OnThisDay)-(datepart(dw,@OnThisDay)+@@datefirst-1)%7,-7)
set @weekend = dateadd(day,datediff(day,0,@OnThisDay)-(datepart(dw,@OnThisDay)+@@datefirst-1)%7,0)

declare @x float,@y float;
select @x = count(distinct change_ID_)
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and Actual_End_Date - Create_Date <=2592000 and (dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend)

select @y = count(distinct change_ID_)
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend

SELECT distinct 62 as MetricID -- Desktop and Laptop Deployments within 30 days of request
,DATEADD(d, - DATEPART(dw, @OnThisDay), @OnThisDay) as ReportWeek
,.75 as Target
,@x as value1
,@y as value2
,@x/@y as metricvalue
,GETDATE() AS Entered
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and Actual_End_Date - Create_Date <=2592000 and (dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend)
--2592000 seconds in 30 days

set @OnThisDay = @onThisDay +7
set @loop=@loop -1
end

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-02-28 : 23:51:51

One way to do is you should have a linked server established and then you can write cross server queries.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-03-01 : 03:49:48

or you can use openrowset.
more: http://msdn.microsoft.com/en-us/library/ms190312.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:28:09

if this is an adhoc reqmnt, you can use OPENROWSET, else linked server is better

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 09:31:04

It is an ad hoc query.....reading how to use openrowset now...from one oldb to another...one issue I've had using JET, is that I'm on 64 bit servers..apparantly JET doesn't work...hopefully, openrowset is not dependent on JET..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:41:58

JET? are you using access server?

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 09:46:00

no...I had tried to write the data into an access file, instead of trying to hook into another database...

my first attempt is throwing an error...

FROM
OPENROWSET('SQLOLEDB',
'il084rem5;'username';'password',
'select @x = count(distinct change_ID_)
FROM il084rem5.dbo.CHG_Change'

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

hints?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:48:27

try like


SELECT @x = a.CountVal
...
FROM OPENROWSET('SQLNCLI', 'il084rem5;'username';'password',
'select count(distinct change_ID_) AS CountVal
FROM il084rem5\il084rem5i2.dbo.CHG_Change') AS a;

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 09:52:27

SELECT a.CountVal
FROM OPENROWSET('SQLNCLI', 'rem5\rem6;'user';'password',
'select count(distinct change_ID_) AS CountVal
FROM rem5\rem6.dbo.CHG_Change') AS a;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'user'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '\'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ') AS a;

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 09:56:47

if u want to access the database in same server or different server
if same server no need to do linked server ....etc
use
insert into pubs.dbo.pubs_authors
select * from testing.dbo.testing_authors

if u wqant to access from differnt server there are many ways

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 09:59:41

from two different servers....please suggest one way..?..

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 10:07:10

do u want the connection to be forever/for just in time

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 10:11:57

SELECT a.*
FROM OPENROWSET('SQLOLEDB','servername';'username';'Password',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-03-02 : 10:13:04

quote:
Originally posted by dlorenc

from two different servers....please suggest one way..?..

I had scenario where I had to transfer tables from 1 database to another. I used linked server function to do this as an sql query. The user would press a button on a webpage and I would transfer the data to another database. It's a bit complicated but I got the code u need if u need help doing it that way I can be of assistance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 10:14:50

quote:
Originally posted by dlorenc

SELECT a.CountVal
FROM OPENROWSET('SQLNCLI', 'rem5\rem6;'user';'password',
'select count(distinct change_ID_) AS CountVal
FROM [rem5\rem6].dbo.CHG_Change') AS a;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'user'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '\'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ') AS a;

try enclosing in square braces

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 10:20:49

try this

declare @x int
SELECT @x = a.CountVal
FROM OPENROWSET('SQLNCLI', 'D3726';'sa';'sa@1234',
'select count(*) AS CountVal
FROM DB.dbo.tablename') AS a;

select @x

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 10:24:22

ame error

SELECT a.CountVal
FROM OPENROWSET('SQLNCLI', 'rem5\rem6;'name';'password',
'select count(distinct change_ID_) AS CountVal
FROM [rem5\rem6].dbo.CHG_Change') AS a;

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'name'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ') AS a;
'.

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 10:26:26

shaggy...if you meant me to do that code exactly...here is the error

Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 10:33:34

hey u havent closed the quote
declare @x int
SELECT @x = a.CountVal
FROM OPENROWSET('SQLNCLI', 'rem5\rem6';'name';'password',
'select count(*) AS CountVal
FROM db.dbo.CHG_Change') AS a;

select @x

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 10:37:59

shaggy, thank you..that solved the error...but apparantly my DBA's will not allow to to go cross databases...??...I'll try a .dtx script....

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-02 : 10:40:33

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources