locked
Inserting values into table. RRS feed

  • Question

  • Hi,

    I need to insert values like following

    http://example.com/105.1.01

    http://example.com/105.1.02

    http://example.com/105.1.03

    ......

    My query is to insert values automatically . For example the value should be like http://example.com/105.1.01and next value it should be take http://example.com/105.1.02. How can i insert values into table 

    Tuesday, March 11, 2014 11:54 AM

Answers

  • You can use Computed column for the same:

    Try the below:

    create Table Test (Id int identity(1,1), URL varchar(100)
    ,CustomURL as URL + Cast(id as varchar(100)))
    
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    
    Select CustomURL From Test
    
    Drop table Test

    • Proposed as answer by SathyanarrayananS Tuesday, March 11, 2014 12:54 PM
    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:59 PM
    Tuesday, March 11, 2014 12:06 PM
  • JayaKumar, just as a suggestion, your query won't work after the CTE has reached 101 recursions.. It will start throwing errors.. You can use the "OPTION (MAXRECURSION 0)" to avoid that...

    sqlsaga,

    thanks for pointing out :)

    Sivakumar,

    Check the below revised version of code with maxrecursion setting to enable unlimited rcursions (for values more than 100)..

    declare @text varchar(100),@n int
    set @n=4100 -- a limit as needed
    set @text='http://example.com/105.1.'
    
    ;with cte
    as
    (
    	select 1 as id,CAST('01' as varchar) as id_str
    	UNION ALL
    	select id+1 as id,CASE WHEN LEN(id+1)=1 THEN CAST('0'+CAST(id+1 as varchar) as varchar) ELSE CAST(id+1 as varchar) END as id_str
    	from cte
    	where id<@n	
    )
    insert into tbl_name(col_name) --modify as required .. 
    select @text+id_str 
    From cte
    option(maxrecursion 0)


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:59 PM
    Thursday, March 13, 2014 9:45 AM

All replies

  • You need to have the sequence for same kind of URLs or any URLs?

    Say eg: 

    http://google.com/105.1.01

    http://example.com/105.1.02

    http://apple.com/105.1.03

    Tuesday, March 11, 2014 11:58 AM
  • Same kind of urls but i need to insert last value of sequence number. like follwoing

    http://example.com/105.1.1

    http://example.com/105.1.2

    http://example.com/105.1.3............http://example.com/105.1.100.....etc

    Tuesday, March 11, 2014 12:02 PM
  • You can use Computed column for the same:

    Try the below:

    create Table Test (Id int identity(1,1), URL varchar(100)
    ,CustomURL as URL + Cast(id as varchar(100)))
    
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    
    Select CustomURL From Test
    
    Drop table Test

    • Proposed as answer by SathyanarrayananS Tuesday, March 11, 2014 12:54 PM
    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:59 PM
    Tuesday, March 11, 2014 12:06 PM
  • you can use a computed column based on identity field

    see

    http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 12:12 PM
  • Suppose i need to change urls like what you mentioned previous? How can i write query to do that. I want to change only url but not sequence of last number.
    Tuesday, March 11, 2014 12:26 PM
  • Did you try my post, that works for all URLS also...

    create Table Test (Id int identity(1,1), URL varchar(100)
    ,CustomURL as URL + Cast(id as varchar(100)))
    
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://apple.com/105.1.'
    
    Select CustomURL From Test
    
    Drop table Test

    Tuesday, March 11, 2014 12:34 PM
  • Sivakumar,

    In case of insertion of values into a table you can go for usage of computed columns as mentioned above.

    But in case you are need of an alternative approach or need to do this on a temporary basis, you can go for the below approach .. :

    declare @text varchar(100),@n int
    set @n=100 -- a limit as needed
    set @text='http://example.com/105.1.'
    
    ;with cte
    as
    (
    	select 1 as id,CAST('01' as varchar) as id_str
    	UNION ALL
    	select id+1 as id,CASE WHEN LEN(id+1)=1 THEN CAST('0'+CAST(id+1 as varchar) as varchar) ELSE CAST(id+1 as varchar) END as id_str
    	from cte
    	where id<@n	
    )
    insert into tbl_name(col_name) --modify as required .. 
    select @text+id_str From cte

    Pls do note that this gives a set of values that has their last value ALONE getting incremented sequentially.. (Not like an IP address format)


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Tuesday, March 11, 2014 12:37 PM
  • Look up CREATE SEQUENCE 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, March 11, 2014 5:18 PM
  • Sivakumar, Use something like this...

    DECLARE @Counter INT = 1, @i INT = 11, @URL VARCHAR(MAX)=''
    
    SELECT @i, @Counter
    
    WHILE @i > @Counter
    BEGIN
    SET @URL = 'example.com/105.1.'+CASE WHEN @Counter <=9  THEN '0'+CONVERT(VARCHAR(1),@Counter) ELSE CONVERT(VARCHAR(10),@Counter) END
    PRINT @URL
    SET @Counter = @Counter + 1
    END

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Tuesday, March 11, 2014 6:17 PM
  • Did you try my post, that works for all URLS also...

    create Table Test (Id int identity(1,1), URL varchar(100)
    ,CustomURL as URL + Cast(id as varchar(100)))
    
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://apple.com/105.1.'
    
    Select CustomURL From Test
    
    Drop table Test

    Latheesh, your query won't create 01 but instead it will create only 1 for 1-9 values... I don't think thats what he wants...


    • Edited by sqlsaga Tuesday, March 11, 2014 6:37 PM
    Tuesday, March 11, 2014 6:18 PM

  • declare @text varchar(100),@n int
    set @n=100 -- a limit as needed
    set @text='http://example.com/105.1.'
    
    ;with cte
    as
    (
    	select 1 as id,CAST('01' as varchar) as id_str
    	UNION ALL
    	select id+1 as id,CASE WHEN LEN(id+1)=1 THEN CAST('0'+CAST(id+1 as varchar) as varchar) ELSE CAST(id+1 as varchar) END as id_str
    	from cte
    	where id<@n	
    )
    insert into tbl_name(col_name) --modify as required .. 
    select @text+id_str From cte

    JayaKumar, just as a suggestion, your query won't work after the CTE has reached 101 recursions.. It will start throwing errors.. You can use the "OPTION (MAXRECURSION 0)" to avoid that...

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Tuesday, March 11, 2014 6:42 PM
  • Did you try my post, that works for all URLS also...

    create Table Test (Id int identity(1,1), URL varchar(100)
    ,CustomURL as URL + Cast(id as varchar(100)))
    
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://example.com/105.1.'
    Insert into Test(URL) Select 'http://apple.com/105.1.'
    
    Select CustomURL From Test
    
    Drop table Test

    Latheesh, your query won't create 01 but instead it will create only 1 for 1-9 values... I don't think thats what he wants...


    Yes, But that was OP wants.See the second post (reply) From OP. :)
    Wednesday, March 12, 2014 12:04 AM
  • JayaKumar, just as a suggestion, your query won't work after the CTE has reached 101 recursions.. It will start throwing errors.. You can use the "OPTION (MAXRECURSION 0)" to avoid that...

    sqlsaga,

    thanks for pointing out :)

    Sivakumar,

    Check the below revised version of code with maxrecursion setting to enable unlimited rcursions (for values more than 100)..

    declare @text varchar(100),@n int
    set @n=4100 -- a limit as needed
    set @text='http://example.com/105.1.'
    
    ;with cte
    as
    (
    	select 1 as id,CAST('01' as varchar) as id_str
    	UNION ALL
    	select id+1 as id,CASE WHEN LEN(id+1)=1 THEN CAST('0'+CAST(id+1 as varchar) as varchar) ELSE CAST(id+1 as varchar) END as id_str
    	from cte
    	where id<@n	
    )
    insert into tbl_name(col_name) --modify as required .. 
    select @text+id_str 
    From cte
    option(maxrecursion 0)


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by Fanny Liu Friday, March 21, 2014 1:59 PM
    Thursday, March 13, 2014 9:45 AM