Using Select 'Insert Into' - 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

Using Select 'Insert Into'

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 07:30:09

Hi

Further to my previous post this is related to what I am doing but not anything with the same problem.

A lot of our tables had data manually inserted into them as time went on, now I am pulling all the data out of the development database and am using this technique to pull out the relevant data:

SELECT 'INSERT INTO TableA (''' + T1.Column1 + ''', ''' + T1.Column2 + ''')'
FROM Dev.dbo.TableA T1
WHERE T1.columnA = <Condition>

This then gives me a pre-built insert into statement.

What I am finding though is that with any date fields (or int/bit etc) I have to use this:

' + CAST(DateAdded AS VARCHAR(21)) + '

And in the insert statement I will get the date as follows:

Jan 1 1999 12:00AM

But from the table it is like this:

01/01/1900

And I want to keep it like this.

How can I pull the value out without changing the date style in my select insert into statement?

Thanks again

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:33:20

use CONVERT(datetime,datefield,101)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:33:50

If you are handling columns with data type DATETIME then you should not do any convert.
But we don't know your table structure so this is only a guess.

No, you're never too old to Yak'n'Roll if you're too young to die.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 07:35:24

I would say you should use CONVERT and use ISO format i.e. yyyymmdd. Don't worry about the format of date being stored in table, because there is no format while STORING date. Formatting is applied only for display purpose.

Harsh Athalye
http://www.letsgeek.net/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:38:10

Did I misunderstood?
I thought the OP is pulling data from one table into a second table, hence I wrote there is NO need to convert.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:40:42

quote:
Originally posted by webfred

Did I misunderstood?
I thought the OP is pulling data from one table into a second table, hence I wrote there is NO need to convert.

No, you're never too old to Yak'n'Roll if you're too young to die.

nope question was to build a query using column values of a table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:46:21

What I can see is a INSERT INTO ...
But I don't see any VALUES(...) or SELECT ...

So how will we know that he has to convert anything or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 07:50:44

Data type is datetime for column

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 07:52:33

quote:
Originally posted by webfred

What I can see is a INSERT INTO ...
But I don't see any VALUES(...) or SELECT ...

So how will we know that he has to convert anything or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

I believe that was typo. OP missed VALUES clause.

Harsh Athalye
http://www.letsgeek.net/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:55:46

quote:
Originally posted by harsh_athalye

quote:
Originally posted by webfred

What I can see is a INSERT INTO ...
But I don't see any VALUES(...) or SELECT ...

So how will we know that he has to convert anything or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

I believe that was typo. OP missed VALUES clause.

Harsh Athalye
http://www.letsgeek.net/

Ok thx for making that clear to me.
Then your answer use ISO format i.e. yyyymmdd is the best way.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:16:11

"use CONVERT(datetime,datefield,101)"

Will only work if USA config in use by the server etc.


CONVERT(datetime,datefield, 112)

will provide 'yyyymmdd' format (WITHOUT hyphens, which is important for unambiguous import!).

or


CONVERT(datetime,datefield, 121)

for 'yyyy-mm-ddThh:mm:ss.sss' ISO8601 format if you need the Time as well as the Date

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 08:42:21

THe following returns the same value as before:

' + CAST(CONVERT(datetime,T3.Start, 121) AS VARCHAR(16)) + '

Not sure, can I use cast and convert in same bit?

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-04-01 : 08:47:47

Here's how I did it guys:

' + CONVERT(VARCHAR,T3.Start, 101) + '

From:

http://www.mssqltips.com/tip.asp?tip=1145

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:49:40

Apologies ...

CONVERT(varchar(8),datefield, 112) to conversion a DateTime to String
or
CONVERT(varchar(23),datefield, 121)

use that INSTEAD of the whole of the CAST you have.

I now realise that what Visakh was refering to was the IMPORT side - given a string in 'mm/dd/yyyy' format THAT can be converted to DATETIME using CONVERT(datetime, SomeStringDate, 101)

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:52:14

"CONVERT(VARCHAR,T3.Start, 101)"

Two potential problems with that:

1) It uses default length for VARCHAR - better to explicitly indicate the required length.

2) It will use mm/dd/yyyy format for the date, and the "import" of that will rely on implicit-cast and that is dependent on Server Setting, Language setting for the currently logged on user, current DATEFORMAT setting and so on ... i.e. "fragile" !!

Hence to use 112 / 121 format to ensure that you are using an unambiguous string date format for SQL Server.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 04:32:13

quote:
Originally posted by Grifter

Hi

Further to my previous post this is related to what I am doing but not anything with the same problem.

A lot of our tables had data manually inserted into them as time went on, now I am pulling all the data out of the development database and am using this technique to pull out the relevant data:

SELECT 'INSERT INTO TableA (''' + T1.Column1 + ''', ''' + T1.Column2 + ''')'
FROM Dev.dbo.TableA T1
WHERE T1.columnA = <Condition>

This then gives me a pre-built insert into statement.

What I am finding though is that with any date fields (or int/bit etc) I have to use this:

' + CAST(DateAdded AS VARCHAR(21)) + '

And in the insert statement I will get the date as follows:

Jan 1 1999 12:00AM

But from the table it is like this:

01/01/1900

And I want to keep it like this.

How can I pull the value out without changing the date style in my select insert into statement?

Thanks again

G

Refer this
http://vyaskn.tripod.com/code/generate_inserts.txt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources