Post reply

What is the Query?

  • May 17, 2012 at 9:10 am

    #261162

    Hi Friends,

    I have SQL statement like

    insert into table1 (column1, column2,column3)

    select column1,column2,'0' as Column3 from table2

    i need to insert the same records but the column3 values as hard coded as '1' like i did for '0'.

    what i am doing is

    insert into table1 (column1, column2,column3)

    select column1,column2,'0' as Column3 from table2

    insert into table1 (column1, column2,column3)

    select column1,column2,'1' as Column3 from table2

    is there any way to do in a single SQL statement to insert hard coded 0 as well as 1.

    may be what i am asking is crazy. but i am just curious....because i deal with 10 million records..so for the first hard coded it takes 30 minutes and for the next it takes another 30 minutes..so i need to do this at the same time...

    any suggestions friends, Sorry if my question is unbearable...

    Thanks,
    Charmer

  • May 17, 2012 at 9:18 am

    #1489101

    Maybe I'm missing something but surely you just run it as a batch or SP?

    I.e.

    CREATE PROCEDURE myProcedure AS

    BEGIN

    insert into table1 (column1, column2,column3)

    select column1,column2,'0' as Column3 from table2

    insert into table1 (column1, column2,column3)

    select column1,column2,'1' as Column3 from table2

    END

    Then

    EXEC myProcedure

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • May 17, 2012 at 9:18 am

    #1489102

    You do realize Books Online can answer your questions if you take the time to read it.

    insert into table1 (column1, column2,column3)

    select column1,column2,'0' from table2

    union all

    select column1,column2,'1' from table2;

  • May 17, 2012 at 10:14 am

    #1489150

    In 2008 and after you can do:

    insert into table1 (column1, column2,column3)

    select column1,column2,n

    from table2, (values (1),(2)) q(n)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 17, 2012 at 10:37 am

    #1489182

    Probably you want to reduce IO cost.

    Following may help if you have enough sql memory available. if not, this may dump the intermediate resultset in tempdb and eventually more costly than 2 inserts

    insert into table1 (column1, column2,column3)

    select a.column1,a.column2, b.val

    from table2 a

    cross join (select 1 val union all select 0 val) b

  • May 17, 2012 at 10:44 am

    #1489192

    Daxesh Patel (5/17/2012)

    Probably you want to reduce IO cost.

    Following may help if you have enough sql memory available. if not, this may dump the intermediate resultset in tempdb and eventually more costly than 2 inserts

    insert into table1 (column1, column2,column3)

    select a.column1,a.column2, b.val

    from table2 a

    cross join (select 1 val union all select 0 val) b

    Don' worry, for this query SQL Server will only dump the intermediate resultset in tempdb if you run on Z80 CPU...:hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 17, 2012 at 10:53 am

    #1489203

    Don' worry, for this query SQL Server will only dump the intermediate resultset in tempdb if you run on Z80 CPU...:hehe:

    You are right, but this query might be just an example, just wanted to convey that this is not always the best option:cool:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply