?
Solved

move some tables from one database to another NEW CREATED database

Posted on 2011-02-25
12
Medium Priority
?
460 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 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 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