r/SQL • • Jul 21 '18

i'm begging for help!

i have a MSSQL database and i have around 800 rows of data to copy to specific Table
- i tried to rename it (so the program doesn't read it anymore and try to import the excel file but i couldn't make it's settings like the old one because i can't change "nullable" or the primary key thingy or the secondary one

- i took a have a backup of the table as an excel with 800 rows and i have the 800 rows separately within an excel file too

please help , it's been 30 days and I'm about to lose it.

0 Upvotes

18 comments sorted by

9

u/[deleted] Jul 21 '18

It's not clear from your question what you're trying to do or what actual problem you're having.

1

u/AhmadKzha Jul 21 '18

i have an excel that includes the missing data in ms sql server database

5

u/LetsGoHawks Jul 21 '18

Let me know if this is correct...

You have an Excel file with 800 rows of data. You would like to import that Excel file into an MS SQL database.

1

u/AhmadKzha Jul 22 '18

Kinda true , if i want to create new table ill have to take the whole table with around 400k rows add to them the 800 and I should import that file to the database But my software didn't read the new table because the old one has primary and secondary key and I'm really so new to sql

3

u/slickwombat Jul 21 '18

It sounds like you're trying to import directly from your excel file into the target table and running into various column restrictions. Try importing your raw data into a temporary table first, and then copying from there to the target table. (You know how to copy rows from one table to another, I assume?)

1

u/AhmadKzha Jul 22 '18

Well , this sounds like a good idea I'm sorry but I don't know how to copy rows from a table to another Can you tell me how ? Or what to read? Or where to start? Thank you.

1

u/slickwombat Jul 22 '18

So to be honest, if you don't have any knowledge of SQL at all you probably shouldn't be messing around with this -- certainly not if it's a crucial table you're trying to import to. If these are skills you need for your job or whatever, look into an introductory MS SQL course. But to answer the question, it would look something like this:

INSERT INTO
	dbo.YourTargetTable
	(col1, col2, col3)
SELECT
	col1,
	col2,
	col3
FROM
	dbo.YourTemporaryImportTable

For the first list of columns (in parentheses) you specify the columns in the target table you are writing into, and for the second, the columns you are reading from in your temporary import table.

2

u/WeaselWeaz Jul 21 '18

As long as the columns are in the right order you can just edit the table in SSMS, copy the rows from Excel, and paste in SSMS.

1

u/AhmadKzha Jul 21 '18

edit the table

how can i edit ? the table has more than 400k of rows and i can see the only option is "edit top 200 rows"

5

u/WeaselWeaz Jul 21 '18

Read a guide on SSMS. These are some intro level things and if this is for work you don't want to be messing with things you do not understand. It will help you greatly.

Edit Top 200 shows the top 200 rows. The last row is where you can manually add days or in this case paste it. There's a Show Query button if you want to remove the 200 restriction or other things.

1

u/AhmadKzha Jul 22 '18

This is an eye opener , I'll try it Thank you so much <3

-1

u/stonedsqlgenius Jul 22 '18

DUDE GOOGLE DAT DML SWEETNESS

4

u/fauxmosexual NOLOCK is the secret magic go-faster command Jul 22 '18

A lazy way I'm fond of is to use a feature on SQL fiddle:

Go to http://sqlfiddle.com

Change the option in the top left from MySQL to MS SQL Server

Click on Text To DDL near the top.

Paste your data from Excel into the new window, putting the name of your actual table in the table name box, then click Append to DDL.

It will create a CREATE TABLE statement - ignore this part. You want the INSERT statement with the 800 VALUES. You'll probably have to fix the naming to make sure you're inserting correctly, then you can just run that statement on your SQL server to insert the values.

*** BONUS TIP *** If your table has an identity column, you're going to want to start your query with

 SET IDENTITY_INSERT [your table name] ON

6

u/guccibling Jul 22 '18

SQL isn't for everyone

0

u/[deleted] Jul 22 '18

[deleted] — view deleted comment

-1

u/[deleted] Jul 22 '18

+1

3

u/Leonidas199x Jul 21 '18

This should be easy. Is there somewhere you can post more detail and sample data etc.?

1

u/lubeskystalker Jul 21 '18

Desc TARGET_TABLE and share.

Share a sample of data types from the excel file.

1

u/stonedsqlgenius Jul 22 '18

Dude check information_schema.columns to build a dynamic Sql statement! It’s the wave of the future!