May 16, 2014 at 8:50 am
Normally, whenever I've wanted to export data to a file (which is very rare that I do), I have used SSMS. Yes, I'm sure there's a way to do it in T-SQL, but I've just done an export using the SSMS wizard.
But that appears to me to no longer be available in SSMS 2012. So, how is it done from SSMS 2012? Or is it only done using T-SQL/DDL?
Kindest Regards, Rod Connect with me on LinkedIn.
May 16, 2014 at 9:05 am
open SSMS, right click db to export from and select export data.
on choose a destination dialog drop down destination and select flat file
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2014 at 11:34 am
I finally figured out that I could get to the Import and Export Data app from the SQL Server 2012 programs on my PC. I've exported the table I want to put into another database, but when I tried doing a BULK INSERT it failed with the following error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ProviderNumber).
Here's the SQL that I'm trying to try and import the data:
bulk insert [ShowTerrell].dbo.Providers
from 'c:\users\rfalanga\documents\Providers.csv'
with
(
fieldterminator = '|',
rowterminator = '\r\'
);
I'm not sure what the problem is now, so am stumped. Perhaps it has something to do with what I'm trying to import into. I'm trying to import the data into a SQL Server 2008 R2 Express database named "ShowTerrell". Does SQL Express not allow that? Or have I specified the BULK INSERT command wrong?
One more thing, I've realized that I should provide some of the data from the Providers.csv file. Here is some of it:
ProviderNumber|Facility|Description|DisplayName|ProviderStreetAddress|ProviderCity|ProviderState|ProviderZip|StaffCode|ProviderPhone|MuniCode|ContractName|Contact|AlternateContact|FaxNumber|NativeAmerLanguage|OtherLanguage|ServicesProvidedMask|EnglishSpanish|VoucherParticipant|ContractIsCurrent|AgeUnder17|Age18To25|Age26To54|Age55AndOlder|CulturalService|GenderService|GayLesbianService|AgeService|OtherMHServices|OtherPopulations|OtherSubstances|OtherServices|Comments|SpecialContract|ApplyServiceEntryLimits|DebitLimit|FaithBased|AcceptingReferrals|ApplyOutcomesReportingLimits
7|1|Milagro Program (UNM - Ob/Gyn)|Milagro Program|2450 Alamo Ave. SE|Albuquerque|NM|87106|6|(505) 925-2493||Milagro Program|6||(505) 275-5125|||142|3|False|False|True|True|True|False|False|False|False|False||Pregnant women||||False|1||False|True|1
8|1|Albuquerque Health Care for the Homeless|Albuquerque Health Care for the Homeless|1217 1st NW|Albuquerque|NM|87125|1|(505) 766-5197||Albuquerque Health Care for the Homeless, Inc.|||(505) 766-6945|||387|0|False|False|False|False|False|False|False|False|False|False||||||False|1||False|False|1
9|1|Youth Development Inc. - Counseling|Youth Development Inc. - Counseling|6301 Central Ave. NW|Albuquerque|NM|87105|1|(505) 873-1604||Youth Development Inc. - Counseling||||||135|0|False|False|False|False|False|False|False|False|False|False||||||False|||False|False|1
11|1|Hogares Inc.|Hogares Inc.|1218 Griegos Rd. NW|Albuquerque|NM|87107|9|(505) 345-8471||Hogares Inc.|10|7|(505) 342-5414|Zuni, Navajo|French, Basic Sign|183|3|True|False|True|True|False|False|False|False|False|False||||||True|0||False|True|1
18|1|Charter-Heights Behavioral Health System|Charter-Heights Behavioral Health System|5901 Zuni Rd. SE|Albuquerque|NM|87108||(505) 265-8800||Charter-Heights Behavioral Health System|||||||0|False|False|False|False|False|False|False|False|False|False||||||False|||False|False|1
19|1|Inner Visions|Inner Visions|6000 Summer Ave. NE|Albuquerque|NM|87110|1|(505) 268-8622||Inner Visions|||||||0|False|False|False|False|False|False|False|False|False|False||||||False|||False|False|1
23|1|BioEnergetic Acutherapy|BioEnergetic Acutherapy|1819 Carlisle Blvd. NE|Albuquerque|NM|87110|1|(505) 256-9504||BioEnergetic Acutherapy|||||||0|False|False|False|False|False|False|False|False|False|False||||||False|||False|False|1
27|1|CRA Counseling Associates|CRA Counseling Associates|3420 Constitution Ave. NE, Suite B|Albuquerque|NM|87106|1|(505) 266-3706||CRA Counseling Associates|||||||0|False|False|False|False|False|False|False|False|False|False||||||False|||False|False|1
Kindest Regards, Rod Connect with me on LinkedIn.
May 16, 2014 at 11:47 am
Are the collations different
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2014 at 2:41 pm
OK, I've made a little progress. When I looked at the data I saw that the first line had the column names in it. I don't work much with CSV files, so I thought maybe that's just the way it is with CSV files. But I thought I'd remove that first column and see if the BULK INSERT would work then. Once I did that I then ran it again and came up with the following:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 20 (VoucherParticipant).
Now I see its complaining about data for a column named VoucherParticipant. That's a nullable bit field, so I'm not sure why it would be complaining about that. I've taken a look at that what might have been inserted into the table, but see no records, so it's complaining about the first line of real data that starts with, "7|1|Milagro Program (UNM - Ob/Gyn)|"
Kindest Regards, Rod Connect with me on LinkedIn.
May 16, 2014 at 6:45 pm
Do your source table and destination table have the same definition?
You are trying to get a field with value 'false' -that's in VoucherParticipant for the first row - into a bit field and sql doesn't know what to do.
If you are unfamiliar with BULK INSERT, this task will be much easier with the 'import data wizard' as Perry was trying to point you towards.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply