Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

move some tables from one database to another NEW CREATED database

Posted on 2011-02-25
12
Medium Priority
?
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

609 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