June 13, 2012 at 8:28 am
Hello All,
I am trying to do an insert into a table that has an int data type with not null. Unfortunately it is not an identity column and I am not supposed to change the DDL for this table . The table has 5 records now and the current value for this column is 5. I need to do an insert starting from the next number which is 6 and I am going to insert 900 records. Is there a way that I can autoincrement it while doing the inserts?
Thanks for your help guys.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 8:32 am
You could build the data up including a row_number function adding 5 so that it starts from 6 and ends at 905.
INSERT INTO
sometable
SELECT
ROW_NUMBER() OVER(ORDER BY intcoloumn ) + 5 AS ID,
Col1,
Col2
....
FROM
someothertable
June 13, 2012 at 8:38 am
Thanks for the reply Anthony. But I got the below error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 8:41 am
Can you provide table deffinition and sample data and your query?
June 13, 2012 at 8:45 am
This was removed by the editor as SPAM
June 13, 2012 at 8:52 am
I am using an ssis package to do the insert. The query I am using is as below:
OLEDB Source with following code:
SELECT (SELECT
ROW_NUMBER() OVER(ORDER BY ID ) + 5 FROM dbo.Accounts) AS ID
,cast(SiteID as int)AS SiteID,cast(substring(SLXID,1,12) as nvarchar(12)) AS SLXID,cast(substring(PK90ID,1,10) as nvarchar(10)) AS PK90ID,
NULL AS [AccountName]
,'2012-01-01 00:00:00.000' AS [Date]
,1 AS [ProductID]
,NULL AS [License]
,NULL AS [CancelOnDate] FROM sourcetable
The destination table definition:
CREATE TABLE [dbo].[Accounts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[SLXID] [nvarchar](12) NULL,
[PK90ID] [nvarchar](10) NULL,
[AccountName] [nvarchar](128) NULL,
[Date] [datetime] NULL,
[ProductID] [int] NULL,
[License] [nvarchar](19) NULL,
[CancelOnDate] [datetime] NULL
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 8:53 am
whats the definition of the source table?
June 13, 2012 at 8:55 am
Anthony Looking at the DDL of the destination the ID column appears to be identity column.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 8:56 am
My Bad.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 13, 2012 at 9:01 am
For arguments sake, I suspect this is the sort of thing you were looking for: -
SELECT
ROW_NUMBER() (ORDER BY (SELECT NULL)) + b.idSeed
cast(SiteID AS INT) AS SiteID,
cast(substring(SLXID, 1, 12) AS NVARCHAR(12)) AS SLXID,
cast(substring(PK90ID, 1, 10) AS NVARCHAR(10)) AS PK90ID,
NULL AS [AccountName],
'2012-01-01 00:00:00.000' AS [Date], 1 AS [ProductID],
NULL AS [License], NULL AS [CancelOnDate]
FROM sourcetable
CROSS APPLY (SELECT MAX(ID)
FROM Accounts) b(idSeed)
However, as you pointed out, it seems your non-identity column is in fact an identity column 😛
June 13, 2012 at 9:17 am
anthony.green (6/13/2012)
whats the definition of the source table?
CREATE TABLE [dbo].[sourcetable](
[SiteID] [numeric](18, 0) NULL,
[SLXID] [varchar](50) NULL,
[PK90ID] [varchar](8000) NULL,
[ChannelID] [int] NULL,
[PlanCalls] [int] NULL,
[Monthly$] [money] NULL,
[OverageRate] [money] NULL,
[Activate] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The issue is I dont have a ID column in the source table to map it in the destination table. Even If i alter the ddl of the source table and add an identity column when I run the package it will error out becuase the identity's wont match. Hence I want to pick the value from the destination table for the ID column and autoincrement it every time the insert happens. Your script is returning multiple values at a time and hence I am not able to do an insert..
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 14, 2012 at 1:10 am
But the table has an identity column so you dont need to do the row_number() + 5.
If it didnt have an identity and you dont have an identity on the source table you would want to look at Cadavre's solution in doing ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) and cross applying the destination table with its max ID.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply