copy paste in sql

Question

copy paste in sql

ckmoied

Ten Centuries

Points: 1092
More actions
December 9, 2007 at 5:44 am

#62891

can we copy paste excel data into sql tables. How ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

No...

But you can write forumulas in the Excel spreadsheet that will create the necessary INSERT/SELECT or INSERT/VALUES statements.

There's also several ways to import the data from a spreadsheet into SQL Tables using OpenRowSet... if you get clever with [Sheet1$] by adding either range names or cell coordinates, you can do some pretty remarkable stuff... see the following URLs...

http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx

http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ckmoied Ten Centuries Points: 1092 More actions · Answer 2

Thanks Jeff,

but its about bringing the data in a new table in sql. what if we want to append the data to an existing table.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Think about it... 😉 See page 3 where they have the following code...

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] )

SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP]

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',

'SELECT * FROM [Sheet1$]')

...I'd say the table already exists there... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

and you can find lot of informations here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail