Can't paste records into SQL table 1

[LakotaMan]

Hi All,

Warning: I am VERY new to SQL Server.

I am trying to paste several records from an Access 2007 table into a new SQL table in SQL Server Management Express 2005. I used this method to transfer records several times this morning with 3 other tables (approx. 160 records) and it worked fine. This time, however, nothing happens when I try to paste. I tested to see if the records were on the clipboard --they pasted fine into MS Word. I restarted SQL Server Management Studio and MS Access --problem still occurs.

I am pulling my hair out as I was hoping to use this easy method to transfer the records from a large table later on.

If I have contacted the wrong forum, I apologize, please direct me to the correct one.

Any help you can give me will be greatly appreciated.

Thanks,
LM

 

[hmckillop]

Its not a particularly great way of doing things for a variety of reasons.

My suggestion would be to create a linked server and then use an INSERT statement. This will give you much greater control in terms of data population and types

exec sp_addlinkedserver @server='AccessDb',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='C:\Documents and Settings\My Documents
\nwind.mdb'

exec sp_addlinkedsrvlogin @rmtsrvname='AccessDb', 
@useself='false', 
@rmtuser='Admin', 
@rmtpassword=''

This means you can then query the linked server directly

INSERT INTO MySqlServerDB.dbo.MYSSQLTable (field1, field2 etc...)
SELECT field1 , field2, ...
FROM OpenQuery (AccessDB , 'Select field1, field2, ... from MyAccessTable)

I think this should do exactly what you are requiring and also permit better control.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[LakotaMan]

hmckillop,

That sounds like a GREAT idea ... stupid question: where do I put that first bit of code? Stored Procedure? Query?

Sorry to be so dumb!

Thanks,
LM

 

[hmckillop]

If you open a new query window in SQL Server 2005
File- > New Query -> open with current connection (or CTRL + N)
(on the basis you already have SQL opened)

You will need to make the changes to the where the access file is located and the queries etc, but should be ok.


"I'm living so far beyond my income that we may almost be said to be living apart