Howto: Create Stored Procedure to copy table from another database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lidschi - 26 Oct 2004 12:49 GMT

Hi @Newsgroup,

I got the following problem. I want to copy a table from Database X to
Database Y in SQL Server. I want to use a stored procedure (Create Table
query in Stored Procedure) in ADP to do the job. I enter
[DatabaseName].[dbo].[NewTablleName]. Access then tells me that I have a
irregular identifier. Can someone please post a sample code.

Thanks in advance for your help.

Regards
Lidschi

Reply to this Message

MGFoster - 26 Oct 2004 19:46 GMT

How about this:

CREATE PROCEDURE usp_CopyTable
AS

/*Assumes the target table already exists
with the same structure as the source table */

INSERT INTO dbo.targetTable
SELECT * FROM db_name.dbo.sourceTable

GO

The database "db_name" must be on the same SQL server instance. Also,
"The login for the current connection must be associated with an
existing user ID in the database specified by db_name, and that user ID
must have create table permissions." (BOL article CREATE TABLE).

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hi @Newsgroup,
>
[quoted text clipped - 3 lines]
> [DatabaseName].[dbo].[NewTablleName]. Access then tells me that I have a
> irregular identifier. Can someone please post a sample code.

Reply to this Message

Lidschi - 27 Oct 2004 08:41 GMT

Hi MGFoster,

yes. that helped. The problem was that I had to code it on the SQL Server
since Access 2003 (german version) couldn't locate the other database stored
on the SQL Server.

But I have another question. Now that I have the table set up I want to
periodically append data from databaseX.tableX to databaseY.tableY.
Can you help me with the code.

Thanks again for your help.

Kind regards
Lidschi.

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 24 lines]
> > [DatabaseName].[dbo].[NewTablleName]. Access then tells me that I have a
> > irregular identifier. Can someone please post a sample code.

Reply to this Message

MGFoster - 27 Oct 2004 19:23 GMT

It's the same syntax:

database_name.owner_name.table_name

IOW, create an SP in the target database that copies from the source
table to the target db & table using the above syntax. E.g.:

INSERT INTO dbo.targetTable
SELECT * FROM db_name.dbo.sourceTable
WHERE ... criteria ...

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hi MGFoster,
>
[quoted text clipped - 26 lines]
>>
>>GO

< SNIP

Reply to this Message

Lidschi - 28 Oct 2004 06:45 GMT

Hi MGFoster,

Problem solved - no more questions. THANK YOU.

Cheers
Lidschi.

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 42 lines]
>
> < SNIP

Reply to this Message

Marina - 25 Dec 2006 14:30 GMT

Frohe Weihnachten!

>Peter auf Frauenjagd:
> http://pe.megalife.cc/de/
>
>Peter hunting girls:
> http://pe.megalife.cc/en/

Reply to this Message