Solved

move some tables from one database to another NEW CREATED database

Posted on 2011-02-25
12
452 Views
Last Modified: 2012-05-11
Is there anyway I can copy a table from one database to another using vb.net code
 having in mind that the table does not exist in the database where I have to move it to.

So the whole idea is to move some tables from one sql database to another NEW CREATED sql database using code.

Thanks
0
Comment
Question by:ankouny
  • 9
  • 3
12 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 125 total points
ID: 34981366
Try execute this query for each table that you want to move to new database

SELECT * INTO NewDatabase.dbo.TABLE2 FROM OldDatabase.dbo.TABLE1

Open in new window

This will create new table in new database and also moves data
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981378
I mean through your code, execute this query, as you execute update queries (using executequery)
This would work only once.
0
 

Author Comment

by:ankouny
ID: 34981835
what about Views ? if I want to copy Views also ?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981875
Glad I could help


That can be done only by execute CREATE VIEW script

Either go to SQL SERVER and right-click view - create script and execute it in new database

Or
execute the create script as it is through your code
0
 

Author Comment

by:ankouny
ID: 34981888
how do you do it through code ?
I need something like the query above ?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981996
You need to generate the CREATE VIEW code and execute it in your code using executenonquery
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34982013
Dim myQuery As String = "CREATE VIEW myView AS ..... END GO"
Execute this query using ExecuteNonQuery as you done for tables query
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34982036
Hope you are getting me

To get the query, as I mentioned before generate the create script query from sql server management studio and use it in your code
0
 

Author Comment

by:ankouny
ID: 34982385
I know this way , but I want user to be able to choose the view from a grid view and after he chooses it I want to create it in the other database, I was looking for something that copies the view from one table to another table.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34982445
I suggest you to post a new question - so that other experts also help you
Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34985906
@ankouny:
It is possible to copy views - you need to use some SDK that does this job into your application
RedGate Tool - Install the trial. You can see the SDK source code in start menu folder.

But you need to buy this SDK after 14 days, if you wish to continue.

OR google for some free SDK that does this job.

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34985939
Now I believe, I found it for you!
http://www.sqlservercurry.com/2010/09/display-view-definition-using-t-sql.html

Execute this query against proper database
SELECT sysob.NAME, sysco.text as [View Definition] 
FROM sys.syscomments sysco 
	JOIN sys.objects sysob ON sysco.id = sysob.object_id 
	JOIN sys.schemas syssh ON sysob.schema_id = syssh.schema_id
WHERE sysob.type = 'V'

Open in new window

This query will return the view name and its create script. Show view name in GUI and when user clicks it execute the create script

That's all - I hope :)

Please let me know, if this helps.
Raj
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now