What is the Query?

• Charmer

  SSChampion

  Points: 14903

  More actions

  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

• derek.colley

  SSCrazy Eights

  Points: 8040

  More actions

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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;

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  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]

• Daxesh Patel

  Hall of Fame

  Points: 3208

  More actions

  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

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  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]

• Daxesh Patel

  Hall of Fame

  Points: 3208

  More actions

  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: