SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 loading data into a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bsieloff
Starting Member

2 Posts

Posted - 09/13/2007 :  13:49:01  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  13:52:13  Show Profile  Reply with Quote
"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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/13/2007 :  13:52:13  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  14:07:15  Show Profile  Reply with Quote
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 - 09/13/2007 :  14:34:17  Show Profile  Reply with Quote
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 - 09/13/2007 :  14:48:34  Show Profile  Reply with Quote


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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  14:51:32  Show Profile  Reply with Quote
"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 - 09/13/2007 :  14:52:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/13/2007 :  15:16:17  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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 - 09/13/2007 :  15:26:49  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  15:28:11  Show Profile  Reply with Quote
"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 - 09/13/2007 :  15:29:15  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  15:31:47  Show Profile  Reply with Quote
"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 - 09/13/2007 :  15:38:23  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/13/2007 :  16:07:56  Show Profile  Reply with Quote
"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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/13/2007 :  16:18:59  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000