how can I insert multiple rows once only using Sql?

Question

how can I insert multiple rows once only using Sql?

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I have exported a data record from Sqlite to Sql Server with Dump. I need to get these records and insert inside a new table that I have created to receive these records inside my database on the Sql server. The problem is that I have many rows to insert. There are more than 1,000 rows and I would like to know if there is a simpler way of doing this than repeating 1000 times the insert into TableName values ...

ex:

INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office");
INSERT INTO MyTable VALUES ("Billy", 125, "London Office");
INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("roger", 23, "Nuvens");
INSERT INTO MyTable VALUES ("jose", 500, "London Office");
INSERT INTO MyTable VALUES ("elanda", 126, "Paris");

Let's imagine that these insert has more than 1,000 Is there a way to do it in an easier way without repeating 1,000 times?

Here is a print of how the records are:

sql-server

asked by anonymous 25.08.2017 / 19:22

3 answers

3

There are some simpler ways to copy this data:

Linked Server You can make a linked server from the source server on the destination server. This way you can make an insert based on a select.

Importing Data You can import the data into the wizard of the sql server, right-click the target database, go to tasks, and import data, as in the following figure

ManuallyedittherecordsinthetableThereisstillthepossibilitytoright-clickthetargettableintheobjectexplorerandclickonthe"Edit 200 upper rows" item. With this option the table will open as in an excel. And it will be possible to paste the records if they are on the clipboard (either as a grid source, select result or an excel).

25.08.2017 / 19:37

1

Go to your sqlite and make a query that generates queries:

select 'insert into ..... values ('+Campo+','..... from sua tabela....

The result should have the 'load script'

Another option is to export the database structure using the SQLite Browser , using the Export to SQL .

EDIT

You can use the result of a select to include records in a table:

insert into tabela values select campos from tabela.....

25.08.2017 / 19:34

How to clear an input when clicked? How to inherit more than one class in PHP?

score 0 · Accepted Answer

So, with your help, I have been able to solve this mystery. to replace it in the right place, I had to use a regular expression on the site link to test if it could find exactly where I should do my replace. My regular expression in my case was VALUES. *, ([0-9] {5}), 5-digit. '

After it found I only replaced with values I wanted in a row and I used the Notepad ++ macro as it was recommended by someone here to repeat the same action for all the lines and then play in the sql and execute the records and insert in the bank.