November 26, 2009 at 1:43 pm
Could anyone tell me how to use T-SQL to copy tables (structure and data) between servers?
Thank you.
November 26, 2009 at 1:47 pm
You wanted to purely use T-SQL only to do this?
Not contemplating idea of SSIS to do this?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 2:14 pm
yes, sir!
November 26, 2009 at 2:43 pm
You need to script all the database objects on the source server and create them on destination server. After which you can uses BCP or use Linked Server tomove the data.
How large is the database?
Why only T-SQL is being considered?
Explanation would be helpful.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 4:46 pm
Is it possible to use T-SQL only?
If the above is yes, could you please show me how to copy (structure + data) table (Table A) from ServerA to ServerB ?
T-SQL way is the one I am interested although I know how to setup SSIS, BCP.... other ways.
Thanks.
November 26, 2009 at 5:01 pm
It needs creation of all the database objects manually and use Stored Procs to move data.
I did not get the answer for the question how large is the database and also are you doing it only one time, or repeat it (Moving the Data)?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 7:04 pm
Thank you again for the response.
I do not have to move all tables of the database, but I have a list which I would do in a loop.
If you do not mind tell me how to do it with just one table (tableA) moving (structure + data) from one databaseA to databaseB (same server) using T-SQL? (whether or not the object table exists)
Is that simple ?
Thanks.
November 26, 2009 at 7:39 pm
In SSMS go to the Database which has your tables.
Right Click the Database -> Tasks -> Generate Scripts.
In the Wizard, click next in the initial screen, in the next screen click next.
In the choose script options step under Table/ View options, see that you select True for the option Script Indexes (by default it is false) This is only required if you want to have the same indexes on the new table also. If not leave it. Click next
In the next step, select Tables by checking the check box. Click next
In the choose tables step, Select All will create scripts for all tables, if you have to choose the tables
individually, then select those tables needed by checking them respectively. Click next when all tables are selected.
This is the last option which gives you a choice to save the script in a file or copy into clipboard or to open a new query window. Choose which one you feel is apt to you.
If you choose to leave the default option it will script all the tables selected and the indexes (if selected) and opens a new query window. You can use this query in the destination database to create these tables.
Upon creating the tables at destination, you can have a Linked server on the destination which will enable you to use a 4 part notation and insert the data into your newly created tables.
if you created a table called table1, then in the Database when you have created these new tables,
use this query,
Insert into Table1
Select * from LinkedServer.DatabaseName.Schema.Table1
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 7:59 pm
Thanks again.
I got the idea already.
How about moving SPs between databases of the same server (or different) using T-SQL?
November 26, 2009 at 8:07 pm
When you got the idea already then why can't you specify clearly what is that you need instead of posting couple of lines.
Do you need how to move the SP's also?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 11:53 pm
Bru's way is a better approach but just in case I'll offer an alternate, the only advantage being how few lines of code it needs. It assumes you store the list of tables you want to copy over in a table named IMPORT_TABLES.
DECLARE @tablename VARCHAR(50)
DECLARE crsr CURSOR FOR SELECT table_name FROM IMPORT_TABLES WHERE ACTION = 'IMPORT'
OPEN crsr
FETCH NEXT FROM crsr INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @tablename + ']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)drop table ' + @tablename)
EXEC('select * into dbo.temp_' + @tablename + ' from [SERVER_TO_EXPORT_FROM].DATABASE.SCHEMA.TABLE' + @tablename)
FETCH NEXT FROM crsr INTO @tablename
END
CLOSE crsr
DEALLOCATE crsr
Obviously if you're importing a large number of tables you wouldn't want to use a cursor, but then, if you were importing a large number of tables you shouldn't even be using only T-SQL in the first place, you should look at SSIS or some kind of ETL solution.
Good luck.
November 27, 2009 at 9:01 am
Yes, please.
November 30, 2009 at 5:16 pm
Should I open another case?
Otherwise, can anyone tell me how to export SP by using T-SQL ?
Thanks.
November 30, 2009 at 5:55 pm
You can use SSMS to generate scripts for all the stored procedures and then execute it on as many servers as necessary. That's not doing it through straight T-SQL, but it may meet your requirements. You never really said if it was sufficient before.
November 30, 2009 at 7:21 pm
Thank you again.
If you do not mind (the last time for this object), is it possible using T-SQL ONLY to obtain the script of a SP just like the way you mention using a manual way to extract/retrieve the content of a SP w/o involving SSIS ?
That's it for this.
THANK YOU ALL!:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply