Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 loading data into a database

Author  Topic 

bsieloff
Starting Member

2 Posts

Posted - 2007-09-13 : 13:49:01
Greetings,

I am new at SQL and I have been asked to load some data into a database. I was given a file that has an extention .sql. shown below are the first few lines

quote:
TRUNCATE TABLE henrylee.[HenryLee]
GO
INSERT INTO henrylee.[HenryLee] ( [custno], [company], [address1], [address2], [city], [state], [zip], [phone], [email] ) VALUES ( 'C00001', 'SUPREME NOVELTY', '5954 S PULASKI ROAD', '', 'CHICAGO', 'IL', '60629', '', '' )

several more rows after this


This looks to me like it was built to be scripted in or use some function of SQL to create and populate the table... does that make sense? Anyway, is there an easy way to insert this data into a table?

Thanks
-B

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 13:52:13
"TRUNCATE TABLE henrylee.[HenryLee]"

This assumed that you have a database called "henrylee" and a table within that database called "HenryLee".

If you haven't then this script is about as much as a chocolate tea-pot

You could globally find & replace
henrylee.[HenryLee]
with the name of your database / table. But if you don't already have a suitable table created then this won't be much use without one - you can't really guess what datatype / size / Primary key / etc. the original table had.

Kristen


Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 13:52:13
unless it's in a csv file with only the data in it, no

that is the easiest, just open in ssms or QA and execute.. but i suggest you backup the table before executing it..

select *
into henrylee.henrylee_backupdate
from henrylee.henrylee

in case henrylee is fooling around with you

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:07:15
I presume that "henrylee" is representative of the data it contains and follows your normal table-naming-conventions?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:34:17
Are you creating a table per person?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

bsieloff
Starting Member

2 Posts

Posted - 2007-09-13 : 14:48:34


there is a database and table already created, and yes the name is henrylee. I'm not the designer just the maintenance boy ;-)

therefore you are saying I can;
open management studio
click on new query
<type> exec c:\henrylee.sql
click execute

and the data should be loaded correctly witht he way the file is written?

thanks for the help
-B


Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:51:32
"I'm not the designer just the maintenance boy"



It should be more like this:

Take backup!
open management studio
click on new query
File Open c:\henrylee.sql
click execute
Check and Restore from backup if something-terrible-happened

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:52:37
No, that's not how it works

Open the *.sql file in SSMS and then just execute it



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 15:16:17
no, if something wrong happens, you're the escape goat


--------------------
keeping it simple...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:26:49
BEGIN TRAN

DML

--ROLLBACK



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 15:28:11
"BEGIN TRAN

DML

--ROLLBACK
"

Although risky if there are any GO statements in the DML

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:29:15
quote:
Originally posted by Kristen

"BEGIN TRAN

DML

--ROLLBACK
"

Although risky if there are any GO statements in the DML

Kristen




Unlike Oracle

TEST



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 15:31:47
"Unlike Oracle"

Really? Well that would be good-to-have in SQL Server too ...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:38:23
No, in Oracle, the trans is not committed until you specially say so.

If you create a transaction, and close the connection, it does bnot commit until you say so, or change the settings



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 16:07:56
"No, in Oracle, the trans is not committed until you specially say so."

And a syntax, or other terminal error, doesn't abort the transaction - leaving the rest of the statements outside a transaction block (and thus implicitly committed)?

Can't understand why SQL Server doesn't have that; very dangerous IMHO

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 16:18:59
yes very... an uncommitted transaction done on the 'right' tables will leaves the application (some parts) in hanged state... blocks even

--------------------
keeping it simple...
Go to Top of Page
   

- Advertisement -