CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
EdgarL
EdgarL

Reputation: 1

MSSQL: Select into: varchar(max) to date - Can't get it to work

I'm currently working on a project to load legacy data from source tables into target tables in MSSQL. I can't use SSIS due to company restrictions so I need to use T-SQL and preferably in a stored procedure.

I have the legacy tables containing only varchar(max) fields and a target table which has all the right datatypes.

Theres a couple of the source tables that contain date data which is stored in varchar(max) and which needs to be loaded into date format fields. This preferably needs to happen via 'select into' types of queries in SQL Server 2008

What I've done is a simple query: INSERT INTO [SRC].[TargetTable] ( col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 ) SELECT accr_nr, address, number, addition, postalcode, city, close_date, personnr, discount, clientnr FROM [STG].[oldtable]

Thanks!

close_date contains varchar data in mm/dd/yyyy format

Upvotes: 0

Views: 296

Answers (1)

Moumit
Moumit

Reputation: 9630

The simple query ..

--SET IDENTITY_INSERT Employee ON

 INSERT INTO  DemoDB1.dbo.Employee(MayBeIdentityId,Name,close_date)
 SELECT MayBeIdentityId, 
        Name,
        CONVERT(datetime,close_dateVarColumn)close_date 
 From DemoDB2.Dbo.Employee

--SET IDENTITY_INSERT Employee OFF

Note: Both db should reside on same server and user must have both db access

Upvotes: 0

Related Questions