September 11, 2009 at 8:43 am
So there are obviously a million and one ways to copy a table between two databases, but I'm kinda curious what people feel the easiest way is.
I find myself quite often having to copy a table that I've created and modified multiple times from our dev database over to our live, or vice versa. I've tried a bunch of different methods, but in each case I find myself thinking that there should be a better way to do it.
The solution should:
Copy the exact table structure including any constraints.
Copy triggers on that table
Copy all the data in the table
Anyone have a method they think is the clear winner?
September 11, 2009 at 9:08 am
My preferred method is to script the table schema in the source database and use the Import/Export Wizard to move the data. I either run the script in a query window in the destination database to create the table or I paste the Create script into the SQL window in the Wizard.
I think this is one of those tasks where the best method depends on what you're comfortable with.
Greg
September 11, 2009 at 11:01 am
agree with Greg.
regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
September 11, 2009 at 11:09 am
Greg Charles (9/11/2009)
My preferred method is to script the table schema in the source database and use the Import/Export Wizard to move the data. I either run the script in a query window in the destination database to create the table or I paste the Create script into the SQL window in the Wizard.I think this is one of those tasks where the best method depends on what you're comfortable with.
Good idea... probably good to create backup tables when needed using this method within the same database too I would think while maintaining the table structure, keys, indexes, constraints, etc.... I know you could use a select into... but boy... do i have a frightening story about that....I dont think im ever using that anymroe, even though its efficient it can be dangerous... specially when working with production tables...and in situations where you to fall back to your backup.. which doesnt have the key/constraint/indexex = you're fired.
--
:hehe:
September 13, 2009 at 8:28 pm
Looks like one of the routine tasks that a dba\dev would do right? i would create a dynamic sql script based on the metadata tables (system tables) that would build me (ONLY BUILD ME, i.e. PRINT) a set of DDL for the operations i want. I can have a parameter for tablename and a param for db name just to make it even more useful. Thus creating a very handy and useful script that i can use anytime to save time and be efficent.....any takers??
September 14, 2009 at 6:39 am
Creating dynamic query for reuse looks like a good idea. If you are doing it adhoc basis, then what I do is generate CREATE TABLE script along with triggers and indexes with required modifications and do a INSERT INTO SELECT FROM whenever possible( I mean whenever the table I have to populate with data is accessible from the instance from where I have to pick data).
Anyone having or trying to create the the dynamic script for this task 🙂
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 14, 2009 at 7:58 am
vishal.gamji (9/13/2009)
Looks like one of the routine tasks that a dba\dev would do right? i would create a dynamic sql script based on the metadata tables (system tables) that would build me (ONLY BUILD ME, i.e. PRINT) a set of DDL for the operations i want. I can have a parameter for tablename and a param for db name just to make it even more useful. Thus creating a very handy and useful script that i can use anytime to save time and be efficent.....any takers??
This is the direction I was leaning. Before I spent the time to do that, I figured I'd see if anyone had a method that was approximately this easy. (Or maybe had a handy little script to do it that they wanted to share ;-))
suresh kumar Sulakh (9/14/2009)
Creating dynamic query for reuse looks like a good idea. If you are doing it adhoc basis, then what I do is generate CREATE TABLE script along with triggers and indexes with required modifications and do a INSERT INTO SELECT FROM whenever possible( I mean whenever the table I have to populate with data is accessible from the instance from where I have to pick data).Anyone having or trying to create the the dynamic script for this task 🙂
This is the way I normally do it, but it has always seemed a bit clunky to me.
December 1, 2010 at 7:37 am
How would I copy 300 tables from 1 database to another in parallel? I have 8 cpus, so presumably I could copy 8 tables at same time.
Is best practice to drop non-clustered indices on target before copy and recreate them afterwards?
Howard
December 1, 2010 at 10:44 am
ok, as Seth identified, there's plenty of ways to script tables...GUI, SMO, lots of script contributions.
one of my articles out there is how to script a table via TSQL[/url]. by scripting in TSQL instead of calling SMO or some other method, i can insert the scripts into a table, dump them via bcp in a job, use them in audits and ddl triggers...there's lots of ways to use it.
Heck I added the call to the function in the SSMS keyboard shortcuts so i just click Control+3 and poof...i get the results of the highlighted tablename. very handy.
It will even script the definition for a temp table. Ever done a SELECT INTO #temp and then wanted the definition?
I wrote the article a while ago, and keep improving the procedure it constantly.
this is the current version:
sp_GetDDLa to script any 2005/2008/R2 Table via TSQL
This procedure will return a multi-row table of results with the definition of any table,function,procedure or trigger...the scripting for a table includes the well formatted CREATE TABLE statement of the table, along with the triggers, rules, indexes and extended properties about the table as well. I have a variant that returns the definition as a simgle varchar(max), specifically for logging into a DDL trigger and stuff like that.
Anyway,once you have the ability to script a TABLE via TSQL and have THAT in place, it's just a matter of using a cursor or loop to whip thru a list of tables or objects...piece of cake.
here's an example i use, which is part of a job that scripts out DDL objects daily. A DDL trigger captures changes that happen between daily script jobs.
This example gets the scripts in dependancy order...so it doesn't blow up because the Child table gets created before the Parent table due to a foreign key.
--note that this script has a dependancy to sp_getDDLa
--http://www.stormrage.com/Portals/0/SSC/sp_GetDDLa2005_V309a.txt
ALTER PROCEDURE sp_export_schema
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #MyObjectHierarchy
(
HID int identity(1,1) not null primary key,
ObjectId int,
TYPE int,OBJECTTYPE AS CASE
WHEN TYPE = 1 THEN 'FUNCTION'
WHEN TYPE = 4 THEN 'VIEW'
WHEN TYPE = 8 THEN 'TABLE'
WHEN TYPE = 16 THEN 'PROCEDURE'
WHEN TYPE =128 THEN 'RULE'
ELSE ''
END,
ONAME varchar(255),
OOWNER varchar(255),
SEQ int
)
--our results table
CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
--our list of objects in dependancy order
INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
EXEC sp_msdependencies @intrans = 1
Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
--synonyns are object type 1 Function?!?!...gotta remove them
DELETE FROM #MyObjectHierarchy WHERE objectid in(
SELECT [object_id] FROM sys.synonyms UNION ALL
SELECT [object_id] FROM master.sys.synonyms)
--custom requirement: only objects starting with KLL
--DELETE FROM #MyObjectHierarchy WHERE LEFT(ONAME,3) <> 'KLL'
DECLARE
@schemaname varchar(255),
@objname varchar(255),
@objecttype varchar(20),
@FullObjectName varchar(510)
DECLARE cur1 CURSOR FOR
SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
OPEN cur1
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
WHILE @@fetch_status <> -1
BEGIN
SET @FullObjectName = QUOTENAME(@schemaname) + '.' + QUOTENAME(@objname)
PRINT @FullObjectName
IF @objecttype IN( 'TABLE','VIEW','FUNCTION','PROCEDURE')
BEGIN
INSERT INTO #Results(ResultsText)
EXEC sp_GetDDLa @FullObjectName
END
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
END
CLOSE cur1
DEALLOCATE cur1
SELECT ResultsText FROM #Results ORDER BY ResultsID
END
GO
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply