move some tables from one database to another NEW CREATED database

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
ankounyAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
I mean through your code, execute this query, as you execute update queries (using executequery)
This would work only once.
0
 
ankounyAuthor Commented:
what about Views ? if I want to copy Views also ?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rajkumar GsSoftware EngineerCommented:
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
 
ankounyAuthor Commented:
how do you do it through code ?
I need something like the query above ?
0
 
Rajkumar GsSoftware EngineerCommented:
You need to generate the CREATE VIEW code and execute it in your code using executenonquery
0
 
Rajkumar GsSoftware EngineerCommented:
Dim myQuery As String = "CREATE VIEW myView AS ..... END GO"
Execute this query using ExecuteNonQuery as you done for tables query
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
ankounyAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
I suggest you to post a new question - so that other experts also help you
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
@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
 
Rajkumar GsSoftware EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.