SQL reusing results in another task...

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

SQL reusing results in another task...

Author

Topic

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-07 : 09:55:28

Hi all, great forum...hope you can help...

I have one master database called "siteadmin", within this database is a table called projects with a field PR_IS_ACTIVE - this is either a Y or N value that tells me whether or not a project is active within QC. There is also a field called "DB_Name" that corresponds to the name of the database for each of the projects. Each project is a seperate database within the sql instance. Hope im making sense...

What I'm after is to use the results of the below query...

SELECT DB_NAME
FROM siteadmin
WHERE PR_IS_ACTIVE = 'Y'

...in a scheduled job. For each database named in the results of the 1st query i want to copy one of the tables out into another database and the table be named the name of the database.

e.g.

SELECT count(*) from [DB_NAME from results here...]

Any help is appreciated...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:00:43

You need to use dynamic SQL

DECLARE @db varchar(100)
SELECT @db=DB_NAME
FROM siteadmin
WHERE PR_IS_ACTIVE = 'Y'

EXEC('SELECT count(*) from '+@db+'..table_name')

Madhivanan

Failing to plan is Planning to fail

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-08 : 03:07:58

quote:
Originally posted by madhivanan

You need to use dynamic SQL

DECLARE @db varchar(100)
SELECT @db=DB_NAME
FROM siteadmin
WHERE PR_IS_ACTIVE = 'Y'

EXEC('SELECT count(*) from '+@db+'..table_name')

Madhivanan

Failing to plan is Planning to fail

Is there anyway I can use the results of the 1st query to run a scheduled job. For every database name listed in the result set I want to goto that database copy a table called "BUG", name the table the name of the database and store it in a database called "reporting"

Effectivly this will give me the Bug table from every project that is active all in a single database called "reporting"

Thanks again all.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 05:12:06

Do you want to copy all data from the table or just sore the name of the table in reporting database?

Madhivanan

Failing to plan is Planning to fail

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-08 : 05:49:22

quote:
Originally posted by madhivanan

Do you want to copy all data from the table or just sore the name of the table in reporting database?

Madhivanan

Failing to plan is Planning to fail

Madhivanan,

I plan to copy all the data from each of the bug tables.

The code I have so far looks like this...

DECLARE @db varchar(100)
DECLARE @id varchar(100)
DECLARE @maxrows int
DECLARE @rownum int
DECLARE @count varchar(100)

SELECT top 1 @db = DB_NAME, @id = PROJECT_ID
FROM td.projects
WHERE PR_IS_ACTIVE = 'Y'

SELECT @maxrows = count(*) from td.projects WHERE PR_IS_ACTIVE = 'Y'
SET @rownum = 0

WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1

EXEC('SELECT * INTO test_reporting.dbo.' +@db+ '_BUG FROM ' +@db+ '.td.bug')

SELECT top 1 @db = DB_NAME, @id = PROJECT_ID
FROM td.projects
WHERE PR_IS_ACTIVE = 'Y' AND PROJECT_ID > @id
END

..Considering this will affect up to 100 databases im concerned how efficient the query is? Also this only works if the "test_reporting" databases is empty - if some of the tables exist then the query fails - is there an easy method I can add to delete all tables from test_reporting before copying across the tables?

Ultimately i want to try and schedule this type of task since i'll be connecting some type of reporting front end.

Thanks again for your help.

Lee

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 06:15:43

This can be simply done

declare @sql varchar(max)
set @sql=''
select @sql=@sql+'insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-08 : 08:06:58

quote:
Originally posted by madhivanan

This can be simply done

declare @sql varchar(max)
set @sql=''
select @sql=@sql+'insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Thanks again Madhivanan,

This is now what i have and it seems to work...

DECLARE @db varchar(100)
DECLARE @id varchar(100)
DECLARE @maxrows int
DECLARE @rownum int
DECLARE @count varchar(100)

SELECT top 1 @db = DB_NAME, @id = PROJECT_ID
FROM td.projects
WHERE PR_IS_ACTIVE = 'Y'

SELECT @maxrows = count(*) from td.projects WHERE PR_IS_ACTIVE = 'Y'

SET @rownum = 0

WHILE @rowNum < @maxRows

BEGIN
SET @rowNum = @rowNum + 1

EXEC('INSERT INTO test_reporting.dbo.all_bugs SELECT * FROM ' +@db+ '.td.bug')

SELECT top 1 @db = DB_NAME, @id = PROJECT_ID
FROM td.projects
WHERE PR_IS_ACTIVE = 'Y' AND PROJECT_ID > @id
END

Any ideas how efficient this query is if its going to be running against 100 db's each db shoud have no more than 2000 records in the BUG table.

Cheers,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 08:36:11

Have you tried the code I posted?

Madhivanan

Failing to plan is Planning to fail

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-08 : 08:52:34

quote:
Originally posted by madhivanan

Have you tried the code I posted?

Madhivanan

Failing to plan is Planning to fail

Hi I tried you cod ebut it gives me the following error:

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

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:09:17

Try this

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

g0dd3rz
Starting Member

6 Posts

Posted - 2010-06-08 : 09:15:17

quote:
Originally posted by madhivanan

Try this

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Works Perfectly - you managed to cut my 20 lines down to 4.

Excellent - thanks !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:23:25

quote:
Originally posted by g0dd3rz

quote:
Originally posted by madhivanan

Try this

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Works Perfectly - you managed to cut my 20 lines down to 4.

Excellent - thanks !

You are welcome

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:25:16

There must be small change

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' insert into test_reporting.dbo.'+DB_NAME+'_BUG SELECT * FROM '+DB_NAME+'.td.bug' from std.projects WHERE PR_IS_ACTIVE = 'Y'

exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources