Solved

move some tables from one database to another NEW CREATED database

Posted on 2011-02-25
12
453 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

911 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

19 Experts available now in Live!

Get 1:1 Help Now