Can you build an select insert into statement without using the values@

Question

1.00/5 (1 vote)

See more:

so in my statement I have:

Select first_name from [table1] insert into [table2] (ID, First_name) values (@ID, @first_name)

but if I use the code this way I have to place values into addwithvalue @ID and @first_name.

All I want to do is copy information from one table to the other.

what can I do to remedy this?

What I have tried:

I have been using parameters, but I am not sure that I can do that with this type of statement. I have tried a datareader but it's only giving single values where I need to copy all this and transfer it to another table.

Posted 4-Sep-17 6:37am

Member 11856456

Updated 12-Sep-17 20:02pm

Add a Solution

Comments

PIEBALDconsult 4-Sep-17 12:59pm

That doesn't look right. Why aren't you doing:
INSERT INTO [table2] (ID, First_name) SELECT ID, First_name FROM [table1]

Member 11856456 4-Sep-17 21:01pm

I will switch out the statement. I will try it this way, I am running a dual statement because I am getting information from one table into another database and bringing it over to be used in a secondary database

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2017-09-04T07:00:00

Solution 1

Yes you can !

SQL

SELECT * INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Posted 4-Sep-17 7:00am

RickZeeland

Comments

PIEBALDconsult 4-Sep-17 13:01pm

That creates a new table.

RickZeeland 4-Sep-17 13:08pm

Your are right, that might not be what he wants !

Member 11856456 5-Sep-17 10:41am

I dont mind to create a new table at this point. How would you use the select into if you are using 2 databases? I dont know how to use 2 connection strings and make the work together in vb.net

RickZeeland 5-Sep-17 12:09pm

If you want to use 2 databases, place the target in [IN externaldb].
I think you can use one connection string for SQL Server, as long as the databases (catalogs) are on the same server, do not specify a 'catalog'.

Member 11856456 5-Sep-17 12:22pm

could you please give an example as I have not dealt with using 2 databases before. I appreciate the help.

Member 11856456 5-Sep-17 12:30pm

here is the actual code that I am running based on your suggestion:

Dim tabname As String
tabname = InputBox("Name of your new table", "new table", "", 1, 1)

Dim sqlconn1 As New SqlConnection(connect1)
Dim sqlconn2 As New SqlConnection(connect2)

sqlconn1.Open()
sqlconn2.Open()

Dim cb2 As New StringBuilder("Select * into " + ComboBox2.Text + ".dbo." + "[" + tabname.ToString + "] [IN externaldb] from " + ComboBox1.Text + ".dbo." + "[" + ListBox1.SelectedItem.ToString + "]")

Dim sqladp = New SqlDataAdapter

sqladp.SelectCommand = New SqlCommand(cb2.ToString, sqlconn1)

sqladp.SelectCommand.ExecuteNonQuery()

sqlconn1.Close()
sqlconn2.Close()

End Sub

figured this may help

RickZeeland 5-Sep-17 12:37pm

I think the example query I mentioned can only be used directly in SQL Server, not from VB.NET, the problem is that you can connect to the server only, but you can not use two databases at the same time. Sorry if my answer confused you.
What is possible e.g. is to connect to the Server and query the databases that are available and list them in a ComboBox.

Member 11856456 5-Sep-17 12:45pm

I am not sure how to query 2 databases at the same time, I am only used to doing one. is there an example somewhere or maybe a quick example you can give that could get me started in the right direction? Did not figure trying to move table information from one database to another would be so hard.

RickZeeland 5-Sep-17 12:48pm

I'm afraid it is not possible to use 2 databases at the same time from VB.NET.
You might try using a stored procedure, this way SQL Server should be able to access more databases. Good luck !

Member 12536859 · Answer 2 · 2017-09-12T20:02:00

Solution 2

INSERT INTO [table2] (ID, First_name)
SELECT id, first_name FROM [table1]

Posted 12-Sep-17 20:02pm

Member 12536859