SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help on SQL script


Need help on SQL script

Author
Message
jds-685721
jds-685721
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 36
Hello, I am learning SQL and wondering if someone can help me with script below – not sure if I am posting it in correct session

I have a table TBL1 with data like below
Col1, Col2, Col3 Col4, Col5
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …
Tons of data…

Now I want to read through TBL1 and insert some rows where Col1 =1 with data below
3 2 Val_A Val_B Val_C
3 2 Val_D Val_B Val_C
3 2 ….
So basically, I want to duplicate the rows and just replace the first column with new value (replace 1 with 3)
BTW, Col1, COl3, Col4, Col5 are the keys od the table

Thanks in advance
James
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 979
Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.

With that being said you could do the following:
insert TBL1
select 3, Col2, Col3, Col4, Col5
from TBL1
where Col1 = 1



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5940 Visits: 5084
Are you sure that rows
Col1, Col2, Col3 Col4, Col5
3 2 Val_A Val_B Val_C
3 2 Val_D Val_B Val_C
are not already existing rows?

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32801 Visits: 18559
Keith Tate (2/3/2014)
Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.

With that being said you could do the following:
insert TBL1
select 3, Col2, Col3, Col4, Col5
from TBL1
where Col1 = 1


That is basically what I would do as well.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jds-685721
jds-685721
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 36
Thaks all for the help.
It's works great! Now can I ask more?
Sorry for not adding them all in once - just try to make it simple for myself Smile

Col1, Col2, Col3 Col4, Col5
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …

Now I want to modify the Col2 base on the Col4’s value(Bal_B)
So it would be like this:
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …
3, Val_B, Val_A, Val_B, Val_C
3, Val_B, Val_D Val_B, Val_E
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 979
You get one freebie without create table statements and sample data :-D. Please read the article that I mentioned and add the necessary information to the post and someone will be able to answer your question.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32801 Visits: 18559
jds-685721 (2/3/2014)
Thaks all for the help.
It's works great! Now can I ask more?
Sorry for not adding them all in once - just try to make it simple for myself Smile

Col1, Col2, Col3 Col4, Col5
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …

Now I want to modify the Col2 base on the Col4’s value(Bal_B)
So it would be like this:
1 2 Val_A, Val_B, Val_C
1 2 Val_D Val_B, Val_E
1 2 …..
2 2 …
2 2 …
3, Val_B, Val_A, Val_B, Val_C
3, Val_B, Val_D Val_B, Val_E


Think about the question you are asking and the general tsql constructs. Try to write that out in as close to valid tsql as you can get and post it if it isn't working.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jds-685721
jds-685721
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 36
Here is my revised request again. Please help
CREATE TABLE [dbo].[MYTABLE]
(
[TND_CD] [smallint] NOT NULL,
[ACCPT_FOR_SLS_FG]
[STR_ID] [int] NOT NULL,
[HIER_ID] [int] NOT NULL,
[LOC_ID] [int] NOT NULL
)
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[TND_CD] ASC,
[STR_ID] ASC,
[HIER_ID] ASC,
[LOC_ID] ASC
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #MYTABLE ON
INSERT INTO [MYTABLE] ([TND_CD], [ACPT_PYMT_FLG], [STR_ID], [HIER_ID], [LOC_ID])
SELECT '1', '10','0','0','1' UNION ALL
SELECT '1', '10','0','0','2' UNION ALL
SELECT '1', '10','0','0','3' UNION ALL
SELECT '1', '10','0','0','4' UNION ALL
SELECT '1', '10','0','0','5' UNION ALL
SELECT '2', '11','0','0','1' UNION ALL
SELECT '2', '11','0','0','2' UNION ALL
SELECT '2', '11','0','0','3' UNION ALL
SELECT '2', '11','0','0','4' UNION ALL
SELECT '2', '11','0','0','5'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MYTABLE OFF

-- Get the number of rows in the looping table
DECLARE @RowCount int
SET @RowCount = (Select count(*) FROM [MYTABLE] WHERE TND_CD =1 )
-- Declare an iterator
DECLARE @I int
-- Initialize the iterator
SET @I = 1

Declare @STR_ID int
Declare @HIER_ID int
Declare @LOC_ID int

-- Loop through the rows of a table @MYTABLE
WHILE (@I <= @RowCount)
BEGIN
Select @STR_ID=LOC_ID, @HIER_ID=HIER_ID, @LOC_ID=LOC_ID FROM [MYTABLE] WHERE TND_CD=1
print TND_CD
Print @STR_ID
print @HIER_ID
print @LOC_ID
INSERT INTO [MYTABLER] VALUES(8 ,10,@STR_ID ,@HIER_ID ,@LOC_ID)
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
SET @I = @I + 1
END

Here is what I am expecting to get:
[TND_CD] [ACCPT_FOR_SLS_FG] [STR_ID] [HIER_ID] [LOC_ID]
1 10 0 0 1
1 10 0 0 2
1 10 0 0 3
1 10 0 0 4
1 10 0 0 5
2 11 0 0 1
2 11 0 0 2
2 11 0 0 3
2 11 0 0 4
2 11 0 0 5
8 10 1 0 1
8 10 2 0 2
8 10 3 0 3
8 10 4 0 4
8 10 5 0 5

and this is what error I got:
1
5
10
0
5
Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.
The statement has been terminated.
Row No = 1
1
5
10
0
5
Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.
The statement has been terminated.
Row No = 2
1
5
10
0
5

Looks like my cusor is always pointing to the last record of my selection

Please help and Thanks in advance

James
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5940 Visits: 5084
I think your approach is not good. Even if you manage to resolve it now, you'll have issues later if going this way.
My suggestion is to re-create your table a bit differently:
CREATE TABLE [dbo].[MYTABLE]
(
[ID] int identity(1,1) primary key,
[TND_CD] [smallint] NOT NULL,
[ACCPT_FOR_SLS_FG] /*???*/
[STR_ID] [int] NOT NULL,
[HIER_ID] [int] NOT NULL,
[LOC_ID] [int] NOT NULL
)

Do not create CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
you already have primary clustered key (surrogate). You can create non-clustered key instead of the old clustered key.

Then keep on with your exhibitions.

Regards,
Igor

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
jds-685721
jds-685721
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 36
Thanks for reply
Unfortunately this is our existing table structure – I can’t change it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search