How to use variables when using more than one "use db"

Question

I have a SQL script that I've been working on making more flexible. I need it to be able to insert rows into tables in one DB then create a table and insert rows in another DB on the same server. I can each of these things in two different scripts of course, but I'm trying to figure out how to do it all in one. Here is an example of what I've tried:

    use firmTriad
create table #TempTable (IssueId nvarchar(300),[SQL] nvarchar(300),FirmID varchar(255),[Table] varchar(255),SecurityDelete varchar(255)
        ,SecurityKeep varchar(255))
declare @IssueID nvarchar(300),@SQL nvarchar(300),@FirmID varchar(255),@SecurityCUSIPdelete varchar(255),@SecurityCUSIPkeep varchar(255)
set @SecurityCUSIPkeep = 'AET133A20'
set @SecurityCUSIPdelete = 'AEXVA0559'
set @FirmID = 'firmTriad'
set @IssueID = 'RQST00002652426'
set @IssueID = 'tblSecurity_MSReference_' + @IssueID
insert into #TempTable (IssueId,FirmID,SecurityDelete,SecurityKeep)
select @IssueID,@FirmID,@SecurityCUSIPdelete,@SecurityCUSIPkeep

use PSBackup
select * from #TempTable
declare @Table varchar(255), @PSTable varchar(255)
set @PSTable = 'tblSecurity_MSReference_' + @IssueID
set @Table = @FirmID + '.dbo.' + left(@Table,len(@Table)-16)

print (@FirmID)
set @SQL = 'create table ' + @IssueID + '([RecID],[SecurityID],[MSCUSIP],[LastUpdateDT]
                ,Issue_Key,Issue_Backup_Date,Issue_Backup_Len)'
exec @SQL
insert into sys. (@firmID + '.dbo.tblSecurity_MSReference') --firmTriad.dbo.tblSecurity_MSReference
select [RecID],[SecurityID],[MSCUSIP],[LastUpdateDT],Issue_Key,Issue_Backup_Date,Issue_Backup_Len
from @Table 
where [SecurityID] in(select RecID from firmTriad.dbo.tblSecurity where CUSIP in(@SecurityCUSIPdelete,@SecurityCUSIPkeep))

I can't figure out how to get the variables to carry over between the two use db sections.

martin · Accepted Answer · 2014-06-05 22:06:34Z

1

I see you are creating a materialized temp table (#TempTable) and then doing some SELECT queries and then creating a new table and inserting some data there.

As I understand, your problem is with cross-database variables, right?

Does it help you to know, that this works:

create table PSBackup.dbo.TheNameOfYourTable(
  IssueId nvarchar(300),
  [SQL] nvarchar(300),
  FirmID varchar(255),
  [Table] varchar(255),
  SecurityDelete varchar(255),
  SecurityKeep varchar(255)
);

You do not need to "use PSBackup" in order to create a table or basically do anything in that database. The generic "formula" is:

SELECT * FROM <dbname>.<schemaname>.<tablename>
EXEC <dbname>.<schemaname>.<StoredProcedureName>

The statement "USE PSBackup" changes the context in which you are working in. If you are creating a table and dont provide dbname and schemaname, the SQL server just uses the defaults (which are master and dbo). And of course, if you change the context, the variables are discarded. Think in terms of transactions and it will become clear as day.

The other, non-related thing is, you really shouldn't use reserved names for column names. This can cause all sorts of problems in the future...Try changing the column names:

"[SQL]" to "Query"
"[Table]" to "TBL" or "TableName"

Does this help?

answered Jun 5, 2014 at 22:06

martin

2991 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Thanks for the help, still have a few questions though.

Collectives™ on Stack Overflow

How to use variables when using more than one "use db"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related