Copying Tables and generating new keys

I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.

I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.

If anyone has any insight to this, it would be greatly appreciated. Thanks,

You can use the OLE DB Command to call a stored proc and get an output value, but it tends to slow down execution. Any way to change this to a set based operation?

Copying multiple SPs in SS 2005

I am totally stumped. In SQL Server 2000, I would fire up my EM and right-click on multiple SPs and then Generate Scripts. I then would start QA and run the script on a different DB. This was a very convenient feature to copy SPs over from one DB to other as well as from one machine to other. I can't seem to do the same in SQL Management Studio. Is it possible? If yes, how?

Now this may not be the right place to ask but since VS and SS go hand-in-hand, I thought I would ask.

If you right click on the database -> Tasks -> Generate Scripts you can select the sprocs you want. I know what you mean though, that exact functionality seems to be missing. If you click on procedures and then go to the summary window you can multiple select but can't script from there. Also, you can right click on each sproc and script to clipboard if you want.
Hth,
Scott|||According to Microsoft's SQL Team:

>> Multi-select script didn't get in till recently – its in post-IDW15 builds and will be in the next public CTP of SQL tools.

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copy SQL express table structure to SQL server

How does one generate Transact-SQL for table structure in SQL server express 2005?

I have very limited access to an SQL server database (using rudimentary web based interface) for my hosted website. I am ready to deploy an application that was developed with Visual Studio 2005 using SQL server express as the database. I have four tables that I want to copy (structure only) from "express" to the hosted SQL server. I think I need to generate Transact-SQL statements to "create" the table structure, then run these statements as a stored procedure on the web based SQL server. I'm having trouble trying to figure out how to generate the code from SQL express 2005. Anyone had any experience with this?

thanks.

Try the link below download and install the eval version as a named instance then register the Express, how right click at the top of management studio and you can generate the create table statement or do an INSERT INTO. Run a search for INSERT INTO and how to register a server in the BOL(books online). Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx