| Author |
Topic |
|
freephoneid
Yak Posting Veteran
52 Posts |
 Posted - 2010-09-21 : 15:10:11
|
| Hi,I need to insert multiple records in history table & I've list of codes to be stored in these table:1234, 5678, 5467, 7685All above code exists in tableA but do not exists in tableB. How can I insert them in one query shot (without using procedure, etc)?Please note that while inserting in tableB, I need to insert the date column field same as the date column field from tableA for that code.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:16:47
|
| Hi Tara!Thanks for the quick reply. I need to insert only certain codes in to tableB, say 1234, 5678, 5467, 7685These codes are input which needs to be inserted in tableB. They are already present in tableA.How can I do this insertion?Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:26:46
|
| Hi Tara!Thanks again for quick reply! Unfortunately, I need to update 500 codes. But I came up with following query:insert into tableB (...., date,...)select ..., date,... from tableA where code in ('1234', '4567', ......)This is working. At any given time, I will not have more than 500 codes to be inserted. Will performance be an issue if I use it as shown above?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:30:53
|
| Actually, I'm creating the variable which contains all comma separated codes which will not be more than 500 at any given time & then using that variable dynamically in the IN clause. Are you saying that it could still cause issue?Note: the codes are input from client (customer)Thanks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:35:39
|
| Hi,I'm getting the codes from the customer through body parameters. I'm implementing this sql in the web service. And the web service gets the code through the body parameter.As I mentioned earlier, I do not want to use procedure or function. Would the query using IN clause work for me?Thanks! |
|
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:38:19
|
| Ok. I've one more question. Before this insert, I need to update one column called status in tableA for each code & then need to perform the insert.Is there a way to combine both update & inserts in one shot? I'll probably consider using proc or func if you can tell me how & whats the best way to do this in one shot! Please note that at any given time, I won't have more than 500 codes.Thanks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 15:51:56
|
| Hi,The codes are already present in tableA. I just need to update the status for inputted codes & then insert them into tableB. I looked at your earlier reply. So, should I use the below function? I'm a kind of newbie to functions!CREATE FUNCTION dbo.udf_Table(@ParmList varchar(8000), @Delim varchar(20))RETURNS @table TABLE (Parameter varchar(255))AS BEGIN DECLARE @x int, @Parameter varchar(255) WHILE CHARINDEX(@Delim, @ParmList)-1 > 0 BEGIN INSERT INTO @table(Parameter) SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1) SELECT @ParmList = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+1, LEN(@ParmList)-CHARINDEX(@Delim,@ParmList)) END INSERT INTO @table(Parameter) SELECT @ParmList RETURNENDGOWhy above function inserts 2 times: one in the loop & one outside loop? Also, how can I modify it so that it'll perform updates too? Thanks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
freephoneid
Yak Posting Veteran
52 Posts |
Posted - 2010-09-21 : 16:01:01
|
| Yes, I do have comma separated list of codes as input. But I'm pretty new to SQL & functions.I need to update the status in tableA for all these codes & then insert them in tableB. Would you be able to post the modified function? Thanks again for answering all my queries! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-09-21 : 16:39:29
|
| >> I need to insert multiple records [sic] in history table <<Row are not records. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html >> I've list of codes to be stored in these table: 1234, 5678, 5467, 7685 <<Do you know the SDQL idioms for Hitory tables? What you posted has no (start_date, end_date) pairs to place it in a temporal space. >> All above code exists in TableA but do not exists in tableB. How can I insert them in one query shot (without using procedure, etc)? <<Unh? Who cares if they are in TableA, if you want them in TableB, just use this: INSERT INTO TableB (unknown_column) -- no DDL, no column name!VALUES (1234), (5678), (5467), (7685);>> Please note that while inserting in TableB, I need to insert the date column field [sic] same as the date column field [sic] from tableA for that code <<Please use real terminology; tables have columns, not fields. The use of "field" in SQL means a part of a temporal data type that loses meaning by itself (i.e. year, month, day, hour, minute, second).Next, why do you want redundant data in your schema? Most of us try to get rid of it. Based on wild guessing, did you mean something like this:INSERT INTO TableB (unknown_column, vague_date) -- no DDL, no column name!SELECT unknown_column, vague_date FROM TableA WHERE unknown_column IN (1234, 5678, 5467, 7685);Please read the FAQ about posting. Then read a good RDBMS and how to remove redundant data in a schema with Normalization. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
Inserting multple records in one shot?