Leading spaces in integer field

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

SQL Server 2005 Forums

Transact-SQL (2005)

Leading spaces in integer field

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-02 : 16:32:14

Hello All,

I am writing a dynamic sql to create the insert statements from a table. I am getting spaces before and after the interger field.

Here is my code and the output. Why is there so much of space between integer field c2?

select 'insert into t1 (c1, c2, c3) values ( ' +
'''' + c1 + ''''
+ ',' ,
+ cast (c2 as varchar(8)) ,
',' +
'''' + c3 + ''''
+ ')'
from t1

output
insert into t1 (c1, c2, c3) values ( 'a', 1 ,'a')

Thanks,
-S

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 17:00:10

If you are looking for the output as you have shown, remove the 2 extra commas (shown in red below).

select 'insert into t1 (c1, c2, c3) values ( ' + 
'''' + c1 + ''''
+ ',' ,
+ cast (c2 as varchar(8)),
',' +
'''' + c3 + ''''
+ ')' 
from t1

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-02 : 20:51:55

I get syntax error then.

select 'insert into t1 (c1, c2, c3) values ( ' +
'''' + c1 + ''''
+ ','
+ cast (c2 as varchar(8))
',' +
'''' + c3 + ''''
+ ')'
from t1

Error:
Incorrect syntax near '+'.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-02 : 20:54:29

Resolved. I forgot to put the '+' in between.

Thanks you Sunitabeck -:)
-S

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 20:57:58

yvw; sorry I missed the plus.

ps: resolves to herself that she will never again post code without first testing it.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-03 : 11:00:00

I can not get the time part of the datetime field. It chops the miliseconds.

for example

If the datetime column c3 has value '2009-12-12 16:14:28.860' in table t1

Then when I run my dynamic sql for insert

select 'insert into tempdb.dbo.t1 values ('
+ cast(c1 as varchar(8))
+ ',' + '''' + c2 + ''''
+ ',' + '''' + CAST(c3 as varchar(32)) + ''''
+ ')'
from tempdb.dbo.t1

I get the inserts as
insert into tempdb.dbo.t1 values (4,'e','Dec 12 2009 4:14PM')

Does not give the seconds and miliseconds.

Any help would be greatly appreciated.
Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 11:05:11

try using this:-


select 'insert into tempdb.dbo.t1 values ('
+ cast(c1 as varchar(8))
+ ',' + '''' + c2 + ''''
+ ',' + '''' + CONVERT(varchar(32),c3,121) + ''''
+ ')'
from tempdb.dbo.t1

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-03 : 11:24:20

Thanks visakh16.

That worked. Superb!

-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:33:50

welcome

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-04 : 07:27:19

Now I am having a problem if the column is of type varchar/char and it does contain the aphostrophy.

For example: column c2 = 'Children's hospital.'

My dynamic insert statement becomes all read after the aphostrophy. It has been treated as end of the value.

select 'insert into tempdb.dbo.t1 values ('
+ cast(c1 as varchar(8))
+ ',' + '''' + c2 + ''''
+ ',' + '''' + CONVERT(varchar(32),c3,121) + ''''
+ ')'
from tempdb.dbo.t1

Should I use CHARINDEX function here?

Thanks,
-S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-04 : 07:30:50

Try

select 'insert into tempdb.dbo.t1 values ('
+ cast(c1 as varchar(8))
+ ',' + '''' + replace(c2,'''','''''') + ''''
+ ',' + '''' + CONVERT(varchar(32),c3,121) + ''''
+ ')'
from tempdb.dbo.t1

Madhivanan

Failing to plan is Planning to fail

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2009-03-04 : 08:43:19

Thanks madhivanaan. Got what I was looking for.

Thanks again
-S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-04 : 09:06:05