September 1, 2006 at 10:42 pm
Hey All,
I've stored procedure for exporting and populating data from databases located on outlets/branch offices to database located on head office.
for example :
i've 5 outlets databases just name it outlet1, outlet2,...,outlet5 and 1 database as data warehouse,name it central.All databases above are located in one machine on head office.
Data collected from each outlets and restored into db server machine on head office with corresponding name.
so, i'm writing a stored procedure with several parameters, one of them is @dbname varchar(20) that represents outlet database name like outlet1, outlet2, and so on.
how can i use this parameter in sql statement form like this :
SELECT
*
INTO
central.dbo.Table1
FROM
some_function_to_convert_varchar_into_dbobject(@dbname).dbo.Table1
--OR Using with cursor
DECLARE MyCur INSENSITIVE CURSOR FOR
SELECT
*
FROM
some_function_to_convert_varchar_into_dbobject(@dbname).dbo.Table1
i've been tried to looking for the function that acts like some_function_to_convert_varchar_into_dbobject function on sql server help.There is only 2 functions i've been founded that is DB_ID and DB_NAME that acts like that,but did'nt return value as db object except varchar and int.
i have planning to place this stored procedure on central database.
thanks for any help.
my english is not good enough, so please forgive me if any faults.
September 2, 2006 at 3:34 am
You can use the sp_executesql stored procedure....
DECLARE @strSQL NVARCHAR(255)
DECLARE @strDBName SYSNAME
SET @strSQL = 'SELECT * INTO central.dbo.Table1 FROM ' + @strDBName + '.dbo.Table1'
EXECUTE sp_executesql @strSQL
--Ramesh
September 3, 2006 at 9:16 pm
Thanks for reply,
in this situation, i want to do some validation on each record being returned by sql
statement before storing the values into destination table.
should i use temporary table in tempdb, or use it inside cursor, or maybe store the values
into variable?any others?
i prefer to use cursor if i can,how about you?
thanks for any help and suggestions.
September 4, 2006 at 12:34 am
what type of validations you want to do?
If you've a simple validations, you can always restrict the rows by having a WHERE clause..
Cursors will never perform better in most of the cases provided you have a large no. of records to process. I prefer to use queries and set based solutions for any type of processing.
--Ramesh
--Ramesh
September 4, 2006 at 1:25 am
Thanks Ramesh,
i've little complex validation (maybe just for me :-)),one of them is :
i want to populate data on table TmpMasterAR with data from table MasterAR.
--MasterAR Table
select
MasterARStoreID StoreID,
MasterARTgl Date,
MasterARGrpCode GrpCode,
MasterARAmount Amount
from
MasterAR
where
MasterARTgl='20050801'
AND MasterARStoreID='PD.BE'
AND MasterARTC=1
--sql statements result
StoreID Date GrpCode Amount
PD.BE20050801100 30100.0
PD.BE200508012 30104.91
PD.BE200508013 30409.0
PD.BE2005080155 3040.90
PD.BE2005080160 30100.0
PD.BE200508019 2736.80
--Thats the query from 1 bill(TC)
descrpition of GrpCode
2 = Net Sales
3 = Gross Sales
55 = Disc Amt
9 = Goverment Tax
...etc
--On TmpMasterAR
select
GrossSales,
DiscAmt,
NettSales,
Tax,
Total
from
TmpMasterAR
where
StoreID='PD.BE'
AND Tgl='20060801'
--the sql statement resultset below here
--------------------------------------------------------------
GrossSales | DiscAmt | NettSales | Tax | Total
--------------------------------------------------------------
4678556 | 216926.40| 4461629.60 | 446162.96 | 4907792.56
that's all.
pls tell me your opinion about this.
Thanks,
M.F.R
nb:
i was successfully solved this problems with using direct temporary table on tempdb, but if any faster/better way to do it, i will be appreciate.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply