August 22, 2019 at 9:47 am
I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of
INSERT INTO myTable
SELECT MAX(table_id) + 1, column2, column3
FROM myTable
WHERE table_id IN (SELECT list of table id's to be replicated);
alas I have circa 200 columns, so I don't want to spend time writing them all down. Instead I INSERT INTO temp table and update the ID there. In Oracle you could define %ROWTYPE variable and simply update table_id column, but nothing of this sort exists in SQL 2014, as far as I know at least.
I wonder if there is a good practice in place on how to copy records?
At the bottom line, what I need to achieve is to replicate existing accounts and some other account related tables, assigning them new account id.
Much appreciated, as always.
August 22, 2019 at 10:40 am
Is the ID column an IDENTITY column?
August 22, 2019 at 10:41 am
IDENTITY would make it simpler indeed. But it is not. Primary Key though.
August 22, 2019 at 10:53 am
If you right-click the table in SSMS then script the table as INSERT, then do the same again and script the tables as SELECT. Paste this SELECT statement at the end of the INSERT replacing the VALUES clause, then just edit that statement a bit to get the current maximum ID and add a ROW_NUM to it.
You should end up with something like this:
INSERT INTO [dbo].[myTable]
(ID
,Col1
,Col2
,Col3
,Col4)
SELECT T.MaxId +ROW_NUMBER() OVER (ORDER BY ID) NewRowNum,
Col1,
Col2,
Col3,
Col4)
FROM [dbo].[myTable]
CROSS APPLY(SELECT MAX(Id) FROM [dbo].[myTable]) T(MaxId)
WHERE Id IN (SELECT list of table id's to be replicated)
August 22, 2019 at 11:45 am
Agree that will work too, yet still would not be a clean solution with over 200 rows.
August 22, 2019 at 12:05 pm
Agree that will work too, yet still would not be a clean solution with over 200 rows.
Do you mean rows or columns?
If you mean columns then you don't actually have to type anything as SQL Server generates the column list for both the insert and the select.
If you meant rows then you have to somehow identify which 200 rows you want to copy.
August 22, 2019 at 12:25 pm
I have meant 200 rows of code
Column1,
Column2,
----
Column200
I appreciate SSMS will generate it for me, yet again, I was hoping to have neater solution, something like one would do in Oracle, which will also work in a future - if at some point a new column(s) will be added.
For now I have addressed it with INSERT INTO #tmp table and updating id with the relevant one.
Thank you for your time.
August 22, 2019 at 12:44 pm
I have meant 200 rows of code
Column1,
Column2,
----
Column200
I appreciate SSMS will generate it for me, yet again, I was hoping to have neater solution, something like one would do in Oracle, which will also work in a future - if at some point a new column(s) will be added.
For now I have addressed it with INSERT INTO #tmp table and updating id with the relevant one.
Thank you for your time.
You can use dynamic SQL to build up your statement. The following will build the list of columns
DECLARE @ColumnList nvarchar(4000);
SET @ColumnList = STUFF( (SELECT N',' + ac.name
FROM sys.all_columns AS ac
WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
ORDER BY ac.column_id
FOR XML PATH('')), 1, 1, N'');
August 22, 2019 at 2:00 pm
I have meant 200 rows of code
Ok, it's normal convention to refer to rows of code as lines of code. What you actually meant was columns as each column is put on a separate line of code. It gets really confusing if you start calling columns rows when you are talking about databases.
December 20, 2021 at 9:50 am
DECLARE @ColumnList nvarchar(4000);
SET @ColumnList = STUFF( (SELECT N',' + ac.name
FROM sys.all_columns AS ac
WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
ORDER BY ac.column_id
FOR XML PATH('')), 1, 1, N'');
Would it be necessary to exclude the Fk columns ? if my table had Fks , also once I would do the INSERT can I INSERT based on the count of rows that occur in the table ( in its existing form ).
For example if a row with TEXT1 = Do Task occurs 5 times , but another row with TEXT1= Do Task for Company ABC occurs 3 times and my objective is to duplicate these rows ( with TEXT1 = Do Task 5 times since it occured 5 times leading to a toal row with with TEXT1 = Do Task to 10 rows ) and so on ? Thanks
December 20, 2021 at 12:11 pm
DECLARE @ColumnList nvarchar(4000);
SET @ColumnList = STUFF( (SELECT N',' + ac.name
FROM sys.all_columns AS ac
WHERE ac.object_id = OBJECT_ID(N'Schemaname.TableName', N'U')
ORDER BY ac.column_id
FOR XML PATH('')), 1, 1, N'');Would it be necessary to exclude the Fk columns ? if my table had Fks , also once I would do the INSERT can I INSERT based on the count of rows that occur in the table ( in its existing form ). For example if a row with TEXT1 = Do Task occurs 5 times , but another row with TEXT1= Do Task for Company ABC occurs 3 times and my objective is to duplicate these rows ( with TEXT1 = Do Task 5 times since it occured 5 times leading to a toal row with with TEXT1 = Do Task to 10 rows ) and so on ? Thanks
I have no idea what your process is doing. I simply provided you with a method to dynamically create a list of column names for a given table.
December 21, 2021 at 5:42 pm
I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of
INSERT INTO myTable
SELECT MAX(table_id) + 1, column2, column3
FROM myTable
WHERE table_id IN (SELECT list of table id's to be replicated);alas I have circa 200 columns, so I don't want to spend time writing them all down. Instead I INSERT INTO temp table and update the ID there. In Oracle you could define %ROWTYPE variable and simply update table_id column, but nothing of this sort exists in SQL 2014, as far as I know at least.
I wonder if there is a good practice in place on how to copy records?
At the bottom line, what I need to achieve is to replicate existing accounts and some other account related tables, assigning them new account id.
Much appreciated, as always.
You could go the way of dynamic SQL but would you really want to move data to a new table (with new accountID's) at the push of a run button , without eyeballing the intricate details ? Like making sure column types are the same.
----------------------------------------------------
July 21, 2023 at 8:55 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply