Copying SQL Stored Procedures (SQL Server 2000)

Hi folks
I am writing a humdinger of a stored procedure, which I can use to
automatically create a second copy of a database, and ensure that the
tables, etc, are all of the same specification.
Here's what I've done so far:
1. Check to see if the second copy of the database exists. If it doesn't
exist, create it.
2. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the first database.
3. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the second database.
4. Compare the two SQL strings to ensure that each table in the second
database is an exact replica of the tables in the first database.
By the end of all of this, the result is that the second database contains
exactly the same tables as the first database, with both sets of tables
being identical. The only exception is that the second database doesn't
have any relationships set up between the tables, although that's to come.
So far, so good. However, when I turned my attention to the stored
procedures in the first database, it all went a bit wrong. I can use the
sysObjects and sysComments tables to build up the dynamic SQL from the first
database that would have to be executed against the second database.
However, I've discovered that it isn't possible to create a stored procedure
in any database other than the one you are currently working with. If I
append "Uses <databasename>" at the beginning of the dynamic SQL string, it
then complains that the "CREATE PROCEDURE" command should be the first
command in any batch process.
Can anyone tell me if it's possible for me to do this?
Incidentally, before anyone suggests it, I've never done any DTS stuff
before, so I'm hoping there are other ways of doing it.
TIA
UK_CodemonkeyHave you thought of backing up your database and
restoring it with a new name?|||Ian
DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
SET @.dbname ='pubs'
SET @.sql ='
CREATE PROCEDURE dbo.nameofSP
AS
SELECT * FROM '+@.dbname+'.dbo.Authors'
EXEC (@.sql)
EXEC dbo.nameofSP
Note: Learn using DTS Packages
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
> Hi folks
> I am writing a humdinger of a stored procedure, which I can use to
> automatically create a second copy of a database, and ensure that the
> tables, etc, are all of the same specification.
> Here's what I've done so far:
> 1. Check to see if the second copy of the database exists. If it doesn't
> exist, create it.
> 2. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the first database.
> 3. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the second database.
> 4. Compare the two SQL strings to ensure that each table in the second
> database is an exact replica of the tables in the first database.
> By the end of all of this, the result is that the second database contains
> exactly the same tables as the first database, with both sets of tables
> being identical. The only exception is that the second database doesn't
> have any relationships set up between the tables, although that's to come.
> So far, so good. However, when I turned my attention to the stored
> procedures in the first database, it all went a bit wrong. I can use the
> sysObjects and sysComments tables to build up the dynamic SQL from the
> first database that would have to be executed against the second database.
> However, I've discovered that it isn't possible to create a stored
> procedure in any database other than the one you are currently working
> with. If I append "Uses <databasename>" at the beginning of the dynamic
> SQL string, it then complains that the "CREATE PROCEDURE" command should
> be the first command in any batch process.
> Can anyone tell me if it's possible for me to do this?
> Incidentally, before anyone suggests it, I've never done any DTS stuff
> before, so I'm hoping there are other ways of doing it.
> TIA
>
> UK_Codemonkey
>|||Uri
I'll give this a bash. Incidentally, I've been administering SQL Server for
the past 4 1/2 years, and have never needed to get into DTS, mainly because
I've been able to do everything through SQL Stored Procedures.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x2PuxnIGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian
> DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
> SET @.dbname ='pubs'
> SET @.sql ='
> CREATE PROCEDURE dbo.nameofSP
> AS
> SELECT * FROM '+@.dbname+'.dbo.Authors'
> EXEC (@.sql)
> EXEC dbo.nameofSP
>
> Note: Learn using DTS Packages
>
>
> "Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
> news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
>

Copying Records into a different table using triggers

I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:

> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:

> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:

>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP