How to Select Row by Row from a table

Question

How to Select Row by Row from a table

Madhusudan Reddy Mandala 1

I have a table in that table has 1 column(Cols) and it has 2 Records see below

I have another table and it has 4 columns see below

I want to insert first table 2 columns into 2nd table FieldName column

How to do that?

Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T10:43:54.337+00:00

Your question is anything but clear. For these type of questions, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with test data, and the expected result of the test data. The test data set should preferably be big enough to illustrate all angles of the problem. This makes it easy to copy and paste into a query window to develop a tested solution.

It also helps if you give a short description of the business rules that explains why you want that particular result.

Finally, include the output from "SELECT @@version".
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T11:39:38.883+00:00

My Question is

For example i have two tables table-1 and table-2.

I have created a Dynamic Stored procedure with 2 parameters that takes two table names.
.
That stored procedure code copies the whatever i added or deleted rows in table-1 it will add all those column names into table-2

If i add or delete column(s) in table-1 after that i execute that Stored procedure it will add or delete the rows into table-2.

Now i want to record all activities like
TableName(Which table is effected after execute Stored procedure),
FieldName(Which is nothing but column name of added in table-2),
OperationType(Added/Deleted message),
Date(Date of execution).

This is the problem
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T12:07:02.133+00:00

I understand even less now. As a starting point, could you post a script with that stored procedure an example of it in action, so that we understand what is is doing?

I would also be curious with the ultimate purpose of this procedure. Passing table names as parameter isn't exactly a vanilla operation.
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T12:52:17.997+00:00

This is the Stored Procedure which takes two table names and it will copies column names whatever i added or deleted in Stagetable to Finaltable

Now, Now i want to Record all activities like
TableName(Which table is effected after execute Stored procedure),
FieldName(Which is nothing but column name of added in table-2),
OperationType(Added/Deleted message),
Date(Date of execution).
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T12:56:05.82+00:00

Please provide the scripts as text, so that we can copy the code and run it. Either include as text or upload a text file.
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T13:08:59.707+00:00

You can download SQL file from here

https://we.tl/t-RAYW5sdA83
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T20:44:24.177+00:00

Sorry for the late reply, but I was busy with other things. Anyway, I don't have anything to add to Viorel's command. Well, it should be labelled an Answer, in case this meets Madhuhsudan's requirements.
Ronen Ariely 15,216 Reputation points

2021-08-23T04:11:16.91+00:00

This is why we have option to "Convert to Answer"

The issue is that this works very poorly and it breaks the entire discussion as it make the comment an answer and remove it from the discussion while all the responses to this message stay in the original position without the message which they respond to! Very poor design but using this forum we can only convert the comment to let the user mark it s answer (and break the discussion thread)

2 answers

Your answer

Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T10:43:54.337+00:00

Your question is anything but clear. For these type of questions, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with test data, and the expected result of the test data. The test data set should preferably be big enough to illustrate all angles of the problem. This makes it easy to copy and paste into a query window to develop a tested solution.

It also helps if you give a short description of the business rules that explains why you want that particular result.

Finally, include the output from "SELECT @@version".
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T11:39:38.883+00:00

My Question is

For example i have two tables table-1 and table-2.

I have created a Dynamic Stored procedure with 2 parameters that takes two table names.
.
That stored procedure code copies the whatever i added or deleted rows in table-1 it will add all those column names into table-2

If i add or delete column(s) in table-1 after that i execute that Stored procedure it will add or delete the rows into table-2.

Now i want to record all activities like
TableName(Which table is effected after execute Stored procedure),
FieldName(Which is nothing but column name of added in table-2),
OperationType(Added/Deleted message),
Date(Date of execution).

This is the problem
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T12:07:02.133+00:00

I understand even less now. As a starting point, could you post a script with that stored procedure an example of it in action, so that we understand what is is doing?

I would also be curious with the ultimate purpose of this procedure. Passing table names as parameter isn't exactly a vanilla operation.
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T12:52:17.997+00:00

This is the Stored Procedure which takes two table names and it will copies column names whatever i added or deleted in Stagetable to Finaltable

Now, Now i want to Record all activities like
TableName(Which table is effected after execute Stored procedure),
FieldName(Which is nothing but column name of added in table-2),
OperationType(Added/Deleted message),
Date(Date of execution).
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T12:56:05.82+00:00

Please provide the scripts as text, so that we can copy the code and run it. Either include as text or upload a text file.
Madhusudan Reddy Mandala 1 Reputation point

2021-08-21T13:08:59.707+00:00

You can download SQL file from here

https://we.tl/t-RAYW5sdA83
Erland Sommarskog 128.9K Reputation points MVP Volunteer Moderator

2021-08-21T20:44:24.177+00:00

Sorry for the late reply, but I was busy with other things. Anyway, I don't have anything to add to Viorel's command. Well, it should be labelled an Answer, in case this meets Madhuhsudan's requirements.
Ronen Ariely 15,216 Reputation points

2021-08-23T04:11:16.91+00:00

This is why we have option to "Convert to Answer"

The issue is that this works very poorly and it breaks the entire discussion as it make the comment an answer and remove it from the discussion while all the responses to this message stay in the original position without the message which they respond to! Very poor design but using this forum we can only convert the comment to let the user mark it s answer (and break the discussion thread)

Answer 1

Viorel 125.7K

Maybe you need something like this:

insert AnotherTable (TableName, FieldName, OperationType, [Date])
select @FinalTable, Cols, 'Added', getutcdate()
from #AddedColumns
union all
select @FinalTable, Cols, 'Deleted', getutcdate()
from #DeletedColumns

Answer 2

EchoLiu-MSFT 14,626

Hi @Madhusudan Reddy Mandala ,

Welcome to the microsoft TSQL Q&A forum!

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Regards
Echo

Share via

How to Select Row by Row from a table

2 answers

Your answer