Solved

move some tables from one database to another NEW CREATED database

Posted on 2011-02-25
12
454 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 97
VB.net and sql server 4 36
Formula for calculating ROI on training 6 28
Need help with a Stored Proc on Sql Server 2012 4 11
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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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