Last post Jan 10, 2016 04:34 PM by rolf muenchbourg
Jan 09, 2016 08:08 PM|rolf muenchbourg|LINK
I have a csv file I want to import into a table to collect emails:
CREATE TABLE [dbo].[emailtest](
[emid] [int] IDENTITY(1,1) NOT NULL,
[email] [varchar](50) NULL,
[daterec] [datetime2](7) NULL,
[status1] [varchar](50) NULL,
[misc1] [nchar](10) NULL,
[misc2] [nchar](10) NULL,
CONSTRAINT [PK_emailtest] PRIMARY KEY CLUSTERED
the csv file has this data comma delimited:
they are not coming from a database, so they don't hav an ID # . I cannot figure out how in SQL StudioMgmt Import Wizard to parse it into this table that has auto-incrementing primary key ids
Thanks in advance for all help
Jan 09, 2016 08:26 PM|Mikesdotnetting|LINK
Use BULK INSERT to import the data. SQL Server will apply emid values automatically.
BULK INSERT emailtest
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
Jan 09, 2016 09:01 PM|rolf muenchbourg|LINK
Thanks for help. I tried this in query, but it doesn't get past the emid instance ::
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 (emid).
Is there something within the WITH clause that we need to add??
Jan 09, 2016 10:03 PM|Mikesdotnetting|LINK
Open the file in Excel and add an emid column to the data (leaving the values blank).
Jan 10, 2016 04:34 PM|rolf muenchbourg|LINK
Yes, needed something the identity insert needed to recognized, just a blank column. Thanks, Mike!