DB Name Aliasing

Question

Post reply

DB Name Aliasing

Russell Robinson

Newbie

Points: 2
More actions
July 2, 2007 at 9:21 am

#62842

I need to find a way to set up some form of 'Global' Aliasing such that I can set up "One" Alias that will be set for all other uses in the same script...
SELECT tAppUser.*
FROM [Mercury Cvt Temp].dbo.tApplicationUser AS tAppUser
INSERT INTO tAppUser(x,y,z) VALUES(@X1,@Y2,@Z3)
INSERT INTO tAppUser(x,y,z) VALUES(@X2,@Y3,@Z1)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I might be missing something, but why not do it in a single set based operation?

INSERT INTO tAppUser(x,y,z)

SELECT x,y,z

FROM [Mercury Cvt Temp].dbo.tApplicationUser AS tAppUser

WHERE ....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Russell Robinson Newbie Points: 2 More actions · Answer 2

Your reply has some help, but the reason I have multiple INSERTs is the script takes 'One' input Table and breaks it out into 4 to 6 other Tables (Depending on a Record type field) and the script also

may use pre-defined Default data or the data from the input file in the INSERT commands,

Also I will have different Input Files (all same structure Format) Imported into different SQL DBs (Separate DTS Packages)

I will have a different script for each Input file, that breaks outs the record types into the other Tables...

So the different Input files may go as follows;

X1.dbo.Input

X2.dbo.Input

And the output may go as follows;

Y1.dbo.out1, Y1.dbo.out2,etc...

Y2.dbo.out1, Y2.dbo.out2,etc...

So I am really trying to write a set of scripts such that I will only need to change one line of code in each script to have the output DB path name set up (Aliased)

So my multiple INSERTs will be something like this

INSERT INTO temp.out1() VALUES()

INSERT INTO temp.out2() VALUES()

So temp is the Alias for the DB Name "Y1.dbo" or "Y2.dbo"

Thus I can copy my script for each new Input file, change one line of code (for temp) and all the rest of the code will use that "temp" definition for the proper DB path

James Horsley Hall of Fame Points: 3196 More actions · Answer 3

I would love the ability to have alias names for both servers and databases - it would make setting up test environments so much easier and also make the migration of code from test to live easier.

For your particular request you might be able to get away with a view though - e.g.

CREATE VIEW vw_tAppUser AS

SELECT tAppUser.*

FROM [Mercury Cvt Temp].dbo.tApplicationUser AS tAppUser

INSERT INTO vw_tAppUser(x,y,z) VALUES(@X1,@Y2,@Z3)

INSERT INTO vw_tAppUser(x,y,z) VALUES(@X2,@Y3,@Z1)

That would be OK on SQL 2000 - can't remember how SQL 7 handled updatable views though if you are still on 7

James Horsley
Workflow Consulting Limited

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

This is available with ORACLE why not microsoft give such an kind of feature to us.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com