Essentially what I want to do is...
Copy a table from my main SQL server database to Temp, starting at a particular row. Ex: Only include row 1,000 to 1,999 (end row.)
I've been using DTS Wizard to CREATE TABLE, and it's working fine, but I searched and searched in google and this forum and I can't find how to "start at particular row" when creating table.
Thanks,
Bill
hi Bill,
tables are created "without" rows... tables are defined by attributes implemented as columns..
rows usually are not numbered, so you can not say "start at row 1000 and go on until row 1999"... you can select data and insert it into other tables via the INSERT .. SELECT statement but, for your "requirement", you have to perform sort of paging.. that's to say skip the first "n" rows and proceed with the remaining..
a simple "solution" to get this kind of "paging" can be performed via ROW_NUMBER() new Transact-SQL 2005 function..
you project the underlying table's data adding a monotonically increasing new integer row number value..
if you write
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); GO DECLARE @.i int; SET @.i = 1; WHILE @.i <= 1000 BEGIN INSERT INTO dbo.TestTB VALUES ( @.i * 10 , CONVERT(varchar, @.i) + 'abc' ); SET @.i = @.i +1 END; GO WITH CTE AS ( SELECT ROW_NUMBER() OVER( ORDER BY Id ) AS rnum, Id, dataValue FROM dbo.TestTB ) SELECT rnum, Id, dataValue FROM CTE WHERE rnum > 50 AND rnum < 100; GO DROP TABLE dbo.TestTB;you get all the rows with rnum > 50 and < 100..
and you can even project+insert that result to another destination table like
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); CREATE TABLE dbo.TestTB2 ( Id int NOT NULL PRIMARY KEY, dataValue varchar(10) NOT NULL ); GO DECLARE @.i int; SET @.i = 1; WHILE @.i <= 1000 BEGIN INSERT INTO dbo.TestTB VALUES ( @.i * 10 , CONVERT(varchar, @.i) + 'abc' ); SET @.i = @.i +1 END; GO WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY Id ) AS rnum, Id, dataValue FROM dbo.TestTB ) INSERT INTO dbo.TestTB2 SELECT Id, dataValue FROM CTE WHERE rnum > 50 AND rnum < 100; SELECT * FROM dbo.TestTB2; GO DROP TABLE dbo.TestTB, dbo.TestTB2;regards
|||Hi Andrea,
I was gone all day, sorry I took so long to respond. I appreciate your reply to my question.
I'm extremely new at this, and this looks like a very long statement. What saying is it's kind of over my head. Would I have to change any other parameters other than the Table names? Please excuse my lack of knowledge.
By the way, I like the name of your company... Insulin Power.
Thanks,
Bill
|||hi Bill
Car54 wrote:
I'm extremely new at this, and this looks like a very long statement. What saying is it's kind of over my head. Would I have to change any other parameters other than the Table names? Please excuse my lack of knowledge.
as you already have "your own" tables, yes, you have to modify them..
the actual statement you have to modify only is
WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY [Id] ) AS rnum, -- modify the eventual order by column [Id], [dataValue] -- modify the returned columns FROM [dbo].[TestTB] -- modify the original table name ) INSERT INTO [dbo].[TestTB2] -- modify the destination table name SELECT [Id], [dataValue] -- modify the columns (returned by the previous Common Table Expression result) FROM CTE WHERE rnum > 50 AND rnum < 100; -- modify the "range" as required
By the way, I like the name of your company... Insulin Power.
I do just hope you do not suffer the same problem
regards
|||Hi Andrea, thank you for posting this. I'm really new at this and I don't know where I would put the name of the table I'm copying, and I'm not sure where to put the number of the row to start at. I apologize for my lack of knowledge. Can you post where I need to enter the rows numbers or anything else I might have to do?
By the way, I didn't realize you were a diabetic and that was the reason for using that name for your company. I'm very sorry to hear that, I have friends that are diabetic.
Thanks,
Bill
|||hi Bill,
WITH CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY [Id] ) AS rnum, [Id], [dataValue] FROM [dbo].[TestTB] ) INSERT INTO [dbo].[TestTB2] SELECT [Id], [dataValue] FROM CTE WHERE rnum > 50 AND rnum < 100;
Hi Andrea, thank you for posting this. I'm really new at this and I don't know where I would put the name of the table I'm copying, and I'm not sure where to put the number of the row to start at. I apologize for my lack of knowledge. Can you post where I need to enter the rows numbers or anything else I might have to do?
[Id] is the column by which you will order the resultset of the CTE you can modify accordingly to your need;
[Id], [dataValue] are the columns you need to select in the CTE to be inserted in the destination table; modify that colum list accordingly to your needs
[dbo].[TestTB] is the original table you need to get data from;
[dbo].[TestTB2] is the destination table;
50 and 100 are the "boundaries" starting from and ending to you like to export..
By the way, I didn't realize you were a diabetic and that was the reason for using that name for your company. I'm very sorry to hear that, I have friends that are diabetic.
fortunately I do have to admit I'm quiet "happy"
Thank you Andrea, and I hope you have a great weekend.
Bill
|||hi Bill,
Thank you Andrea, and I hope you have a great weekend.
Bill
you too
