Howto: Create Stored Procedure to copy table from another database

[Guest]

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

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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).

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6bm4echKqOuFEgEQL5rgCeM6EujcvoVXd2AmJIB/iLqATXIEwAoNzA
6twAi+jJeWptTVpSqvnrzXX/
=BjGe
-----END PGP SIGNATURE-----

 

[Guest]

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

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).

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6bm4echKqOuFEgEQL5rgCeM6EujcvoVXd2AmJIB/iLqATXIEwAoNzA
6twAi+jJeWptTVpSqvnrzXX/
=BjGe
-----END PGP SIGNATURE-----

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.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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 ...

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX/nnoechKqOuFEgEQLJpQCg+jDLImWWIJNSbMdIr4xAeRFW7MIAoPhY
IJX4Cr1VtK4e0PUOVoZDCNI4
=I7uN
-----END PGP SIGNATURE-----

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.

:

< SNIP >

 

[Guest]

Hi MGFoster,

Problem solved - no more questions. THANK YOU.

Cheers
Lidschi.