Importing csv to MS SQL

[oddvalue]

Hi everyone.

I'm relatively new to MS SQL but one of my clients has a site based entirely around it so I am having to get to know it a bit better.

While trying to clean out some of the fake emails from the mail list table they all got deleted.

I have CSV backups of the table, is there any way of importing the CSV into the table with out destroying any new entries into the table?

The table consists of 2 fields, one auto incrementing number and the email address.

The database is on a thirdparty host server and I use SQL Management Studio Express 2005 to manage the database.

Any help at all would be greatly appreciated.

Thanks

Jim

[sknake]

Sure you can. What I would is import the .CSV file to another table then:

SET IDENTITY_INSERT dbo.Email ON

Insert Into Email (IdentityColumn, EmailColumn)
Select IdentityColumn, EmailColumn
From NewEmailTable
Where
NOT EXISTS
(
  Select *
  From Email (NOLOCK)
  Where Email.IdentityColumn = NewEmailTable.IdentityColumn
)


SET IDENTITY_INSERT dbo.Email OFF

You can use that to match identities but more than likely depending on if you had activity before/after the delete and whether or not your table was truncated (which would reset the identity seed value) you may have overlapping identity values with different email data. In this case you could use the same query but test to see if the email exists, and not the identity.

I hope this helps and good luck, data recovery is never fun.

[oddvalue]

Sure you can. What I would is import the .CSV file to another table then:

Thanks for your response, it does help but the trouble I am having is getting that first bit done. How do I actually go about importing the CSV?

[sknake]

Open Microsoft SQL Server Management Studio, expand the server, expand database, Right click on the database --- Tasks --- Import Data, Click next, for choose data source pick "Flat File", navigate to the file, and away you go

[oddvalue]

Open Microsoft SQL Server Management Studio, expand the server, expand database, Right click on the database --- Tasks --- Import Data, Click next, for choose data source pick "Flat File", navigate to the file, and away you go

I don't have that option in my menus. I'm using Microsoft SQL Server Management Studio Express 2005.

Any other suggestions?

[cartman714]

Sure you can. What I would is import the .CSV file to another table then:

SET IDENTITY_INSERT dbo.Email ON

Insert Into Email (IdentityColumn, EmailColumn)
Select IdentityColumn, EmailColumn
From NewEmailTable
Where
NOT EXISTS
(
  Select *
  From Email (NOLOCK)
  Where Email.IdentityColumn = NewEmailTable.IdentityColumn
)


SET IDENTITY_INSERT dbo.Email OFF

You can use that to match identities but more than likely depending on if you had activity before/after the delete and whether or not your table was truncated (which would reset the identity seed value) you may have overlapping identity values with different email data. In this case you could use the same query but test to see if the email exists, and not the identity.

I hope this helps and good luck, data recovery is never fun.

I agree with this just import the .CSV file to another table

[sknake]

You're right, SQL Express does not have an import option. Google on "bcp" or bulk copy and you should be able to import with that. Check out this thread:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/546f4407-0aa4-4b04-96f3-e6f0ba39a9d1

[yangski]

I don't have that option in my menus. I'm using Microsoft SQL Server Management Studio Express 2005.

Any other suggestions?

In the object explorer click the (+) beside the "<Server name>" then the (+) beside the "Databases" then right click the "<name of your database>" select "tasks" then select "Import Data". That's how you do it. I also use SQL Server Management Studio 2005.

[cgyrob]

@yangski

The user said he was using express which does not include SSIS which is used for the import wizard. The link Sknake provided has a blog on the subject and some possible solutions.

[yangski]

Oh I see... Sorry...

[cgyrob]

np...it happens to everyone once in awhile.