December 20, 2013 at 9:38 am
Hello
I need to insert records into a table, like this
TABLE BC
REF | Design | code
001 | teste |0001
001 | teste |0002
001 |teste |00xx , until it reaches 9999
with insert into command i only can insert a record of a time, is there another like in a cicle until code=9999
Thanks in advance
December 20, 2013 at 9:46 am
Hi,
There's no need for a loop. You just need to use a Tally Table. To know what it is and how it replaces a loop, read the following: http://www.sqlservercentral.com/articles/T-SQL/62867/
With your tally table, your code would end like this:
INSERT INTO BC
SELECT '001',
'teste',
RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999
December 20, 2013 at 10:16 am
hi
After creating the tally table, used this, but gives me a error on the select
INSERT INTO BC (ref,design,codigo)
values ("001","teste", SELECT '001',
'teste',
RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999)
thanks for you reply
December 20, 2013 at 10:21 am
That's because you're still using the VALUES clause when you should only use the SELECT.
For more information on INSERT INTO: http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx Check the part Inserting data from other tables
December 20, 2013 at 10:35 am
i put
INSERT INTO BC
SELECT '001','teste', RIGHT( '000' + CAST( n AS varchar(4)), 4)
FROM dbo.Tally
WHERE n <= 9999
and returns this error msg
Erro: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'BC'.
table bc exists
December 20, 2013 at 10:40 am
if i do
select * from tally
returns all the numbers ok 1,2,....11000
December 20, 2013 at 10:42 am
You need to be sure you're in the correct instance.
To be sure you're inserting on the right table, you could use a 3 part name: [db_name].[schema_name].[table_name]
December 20, 2013 at 11:02 am
ok many thanks, works fine, e needed to create table tally on my database not in tempdb like in the example
December 20, 2013 at 11:09 am
You can create it on temdb and use it from any other database by using the 3-part name (tempdb.dbo.Tally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply