select from one table/insert into another in different db/same server 1

[embryo]

I want to create a stored procedure that will select all columns from one row of table 1 and insert them as a new record into table 2 of a different database that is located on the same SQL server.

I'm sure I could use the Query designer in my Access ADP to accomplish my goal, however I cannot set up a Linked Server to connect to the table 2 in the other database while working from my remote location. (firewall issues)

I am also adept at ASP programming, and could do the select and insert from a web page where I've already got both application connectionstrings defined, but would prefer to just execute the procedure on my ASP page and let the server do the work in the background.

Could somebody give me a clue (within a stored procedure) how you refer to a table in a different database, but on the same server?

Thanks-

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[bborissov]

If the DB is located on the same server there is no need to use linked servers, if both tables have the same structure you could use:

INSERT INTO OtherDB.dbo.TableName
SELECT * FROM ThisDBTableName

Of course if you have identity field in the other table you must use FIELD list in BOTH commands:

INSERT INTO OtherDB.dbo.TableName (field list w/o IDENTITY field)
SELECT (the same field list) FROM ThisDBTableName

and if you want to insert even identity field you should use SET IDENTITY_INSERT command:

SET IDENTITY_INSERT OtherDB.dbo.TableName ON 
INSERT INTO OtherDB.dbo.TableName
SELECT * FROM ThisDBTableName
SET IDENTITY_INSERT OtherDB.dbo.TableName OFF

(Check the syntax of SET IDENTITY_INSERT in BOL becuase i'm not so sure that is the right syntax )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[embryo]

Cool, thanks Boris...I'm no longer clueless!

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[markros]

How would you use result of a select statement to create a new table on the linked server?