Can't paste data

I am trying to migrate an Access database to SQL Express.I created the new database in SQL Express, and added the tables and columns.At this point, I have done so without defining any keys, indexes, or relationships.For most of the tables I was able to copy the data from Access and paste same into the SQL Express tables.

However, I am unable to create the last table, which is a link table to manage the many-to-many relationships.

The table has the following columns:

MediumCode (bigint, null)

ArtistCode (bigint, null)

SongCode (bigint, null)

TrackNumber (int, null)

Here is a sample of the data I am trying to paste:

1,180,204,1

2,2,45,1

3,3,80,1

4,4,30,1

5,5,22,1

6,6,108,1

When I perform the paste there is no error message.The data just doesn’t get added to the table.I don’t understand why the other tables worked and this one does not.I am trying to paste 85 rows. Any ideas?

Also I tried to do this as an INSERT query, but SQL Express does not like the following syntax:

INSERT INTO [Music].[dbo].[tblMediumDetails]

([MediumCode]

,[ArtistCode]

,[SongCode]

,[TrackNumber])

VALUES

(1, 180, 204, 1),

(2, 2, 45, 1),

(3, 3, 80, 1),

(4, 4, 30, 1),

(5, 5, 22, 1),

(6, 6, 108, 1)

Do I have to create an INSERT statement for each row? Is there a bulk load function available in SQL Express, or is that not available with the free version?

Thanks,

Robert

Hi,

you either have to create single insert statements or you define a Insert into statement with a following Select statement like:

INSERT INTO SomeTable
(
Columnlist
)
SELECT Columnlist
From Someothertable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the quick reply. Now that I know what needs to be done, I can stop spinning my wheels.

Thanks,

Robert