Creating tables into different database

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

SQL Server 2000 Forums

Transact-SQL (2000)

Creating tables into different database

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-08-26 : 09:57:37

Hi there

Here is my scenario; I have made a view (Query) from database ‘A’ (‘A’ holds all live data) and I want the results to be placed into database ‘CCTD’ as Transactions. Database ‘CCTD’ is shared on the network for reports. I know how to use the ‘into’ command but not into a different database.
I am sure it is something simple and would appreciate the help.

Thanks

SELECT dbo.CALENDAR.[Year], dbo.CALENDAR.Week, dbo.DBSFIL.dbs_store AS Store, SUM(dbo.DBSFIL.dbs_bil_sal) - SUM(dbo.DBSFIL.dbs_bil_ref) AS Trans,
SUM(dbo.DBSFIL.dbs_tot_sal) - SUM(ABS(dbo.DBSFIL.dbs_tot_ref)) AS Sales, SUM(dbo.DBSFIL.dbs_itm_sal) - SUM(dbo.DBSFIL.dbs_itm_ref) AS QTY into Transactions
FROM dbo.DBSFIL INNER JOIN
dbo.CALENDAR ON dbo.DBSFIL.dbs_date = dbo.CALENDAR.CivilDate
GROUP BY dbo.DBSFIL.dbs_store, dbo.CALENDAR.[Year], dbo.CALENDAR.Week
HAVING (dbo.CALENDAR.[Year] > 2001)

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-26 : 10:13:28

SELECT ... INTO databasename.objectowner.tablename FROM ...

Note that you can't use a four part database name as the INTO part of a query, so if you wanted to do a cross-server select into then you would need to be in your output database and remotely select from the source.

-------
Moo. :)

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-08-26 : 10:43:54

Thank-you
worked fine

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-08-27 : 14:14:28

It is interesting, that you posted this. I have a similar situation. I have created a lot of queries, that save data from our Timekeeper database to the enterprise reporting database for reporting. Some are Generated daily, and others are generated on demand. I am just curious to know, what made you create the data in seperate tables for reporting. For me it was performance, and to avoid large table scans for reports. I am starting to Experiment with SQL2000 Analysis Services. Twice as fast for Reporting.

quote:
Originally posted by mufasa

Hi there

Here is my scenario; I have made a view (Query) from database ‘A’ (‘A’ holds all live data) and I want the results to be placed into database ‘CCTD’ as Transactions. Database ‘CCTD’ is shared on the network for reports. I know how to use the ‘into’ command but not into a different database.
I am sure it is something simple and would appreciate the help.

Thanks

SELECT dbo.CALENDAR.[Year], dbo.CALENDAR.Week, dbo.DBSFIL.dbs_store AS Store, SUM(dbo.DBSFIL.dbs_bil_sal) - SUM(dbo.DBSFIL.dbs_bil_ref) AS Trans,
SUM(dbo.DBSFIL.dbs_tot_sal) - SUM(ABS(dbo.DBSFIL.dbs_tot_ref)) AS Sales, SUM(dbo.DBSFIL.dbs_itm_sal) - SUM(dbo.DBSFIL.dbs_itm_ref) AS QTY into Transactions
FROM dbo.DBSFIL INNER JOIN
dbo.CALENDAR ON dbo.DBSFIL.dbs_date = dbo.CALENDAR.CivilDate
GROUP BY dbo.DBSFIL.dbs_store, dbo.CALENDAR.[Year], dbo.CALENDAR.Week
HAVING (dbo.CALENDAR.[Year] > 2001)

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-09-03 : 11:08:32

Hi there
There are a few reasons that I have created different tables.
I am very used to working with MSAccess, and if I were to use the live data, data could easily be altered inside of access. Creating tables inside a different database in SQL, allows me to not have to worry about data corruption.

The main reason is because of the size of the live database. I create tables from queries that reduce the size of the tables.
Example, the main table that I extract data from hold more than 10 million records, while I only need 2 million of those records for my reports.

I also like to create tables that use specific data from multiple tables reducing the time it takes to calculate my reports.

We are now installing Crystal reports, where I will no longer have to worry about data corruption. I will still create tables to increase performance.

Almost all of my tables are created automatically every morning through jobs.
I like you also have manual jobs that I run when needed.

I am learning new things each day with SQL and now Crystal reports, but the bases of all these programs are the same.

Mufasa

quote:
Originally posted by TSQLMan

It is interesting, that you posted this. I have a similar situation. I have created a lot of queries, that save data from our Timekeeper database to the enterprise reporting database for reporting. Some are Generated daily, and others are generated on demand. I am just curious to know, what made you create the data in seperate tables for reporting. For me it was performance, and to avoid large table scans for reports. I am starting to Experiment with SQL2000 Analysis Services. Twice as fast for Reporting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources