Moving data between tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Moving data between tables

Author

Topic

urmas80
Starting Member

20 Posts

Posted - 2012-07-05 : 08:06:41

Hi All,

I has a task, and will be very happy to get some help in it.
I got source table with following data types taht i need to transfer to other table.

TABLE A (Source)
COLUMN A {varchar(50)}
"Customer ID"
"2289"
"5678"
"2223"

COLUMN B {varchar(50)}
"Date"
"01/01/01"
"30/05/02"
"25/09/02"

Expected Result:

TABLE B (Destination)
COLUMN A {int}
"Customer ID"
2289
5678
2223

COLUMN B {date}
"Date"
01-01-01
05-30-02 or 30-05-02
25-09-02 or 09-25-02

As you has noticed i has here few aspects:

1. In column A and B i need to remove the " sign
2. Change the date type (i guess that in column A it's will be automatically as soon as i remove the ").
3. change the date format.. i not sure even how do i start with that...

Thank You in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 08:22:07

[code]INSERT INTO TableB
([Customer Id], [Date])
SELECT
CAST(REPLACE([Customer Id],'"','') AS INT),
CONVERT(DATE, REPLACE([Date],'"',''),103)
FROM
TableA;[/code]If you don't have clean data - for example, if some of the Customer Id's are non-numeric, or if some of the dates are not in the correct format, this would fail.

urmas80
Starting Member

20 Posts

Posted - 2012-07-05 : 08:42:42

Thank you for very quick response and so simple answer!

Looks like it works till it gets to the date, then i get the following error:

Conversion failed when converting date and/or time from character string.

i see that all date is in correct format except of the ["] sign...

what if i leave it in the source format..? can you explain the convert line please?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-05 : 08:53:26

Sorry about that - change it to this:

INSERT INTO TableB
([Customer Id], [Date])
SELECT
	CAST(REPLACE([Customer Id],'"','') AS INT),
	CONVERT(DATE, REPLACE([Date],'"',''),3)
FROM
	TableA;

CONVERT function converts one data type to another. The style (third parameter) conveys information about the format of the input string. Take a look at this page: http://msdn.microsoft.com/en-us/library/ms187928.aspx

urmas80
Starting Member

20 Posts

Posted - 2012-07-05 : 09:05:41

Great!! Thank you, that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources