0

I have deleted some records by mistake, but luckily I know which records those were because they are mentioned in the result of query. I have copied them into excel file. How can I insert all those records back into table, at once?

The additional problem may be that at the time of deleting Result to Text option was checked instead of Result to Grid.

  • I have solved my problem using this tool: http://tools.perceptus.ca/text-wiz.php?ops=7 Since all other answers could probably resolve the problem, I won't accept any answer specific, but thanks for help. –  Oct 27 '17 at 09:12

3 Answers3

0

I would create a formula such as:

replace(replace(replace("([1], [2], [3]),", "[1]", A2), "[2]", B2), "[3]", B3)

Copy this formula down to get all the data in the columns. Note: you may need to add single quotes for strings and dates.

Then go into SSMS and type (with the names filled in):

insert into <tablename>(<col1>, <col2>, . . .)
    values

Then copy in the results of the formula. Remove the last comma and run the code.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I am sorry, I am not sure I am getting this. Can you help me a little more? The table has like 40 columns and I have deleted 283 records total. Should I have as many numbers in the suggested formula as there are columns in the table? And should I put the real name of columns instead of `A2`, `B2`, `B3` and so on? –  Oct 26 '17 at 14:22
  • Since I have the result in a `Text` format, should I copy that result to notepad maybe and then try to `insert` into table from text file? –  Oct 26 '17 at 14:32
  • 1
    Please see this post, I think it can help you - https://stackoverflow.com/questions/16818796/generate-sql-insert-script-from-excel-worksheet – Sandy Oct 26 '17 at 14:35
  • @Sandy Thanks, I am going to consider it –  Oct 26 '17 at 14:38
0

Well, this is a total hack, but it might be worth a try. If it works, it'll be really fast. If it doesn't, you won't have wasted much time.

Create an empty duplicate of your target table. (Right click the table name in SSMS, then Script Table As -> CREATE To -> New Query Editor Window; then change the name of the table, ditch indexes, etc. Just make a heap.)

Once your copy of the table exists, right click on that and select Edit Top n Rows.

Go to Excel. Highlight all of the cells you want to bring over and Copy them to the clipboard.

Back to SSMS. Select the full first (empty) row in the editor window (click on the arrow to the left of the row).

Hit Paste.

Depending on what kind of data you have, there's a fair chance it'll drop right into your heap. From there, you can just do an INSERT back into your base table.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
0

I’d suggest saving your excel, right clicking on your database in the object explorer, going to tasks, and import data. The wizard is fairly easy to use and the most natural least hacky way to get the data back in. Try poking around here for more info...

https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-excel-data-source-sql-server-import-and-export-wizard

calamities
  • 78
  • 3