I am inserting new records stored in SQL Server into a legacy MS Access application using SSIS. During the transformation, I need to get the ID MS Access assigned to the autoincrement column in the MS Access table I am inserting the row into. Is this possible? Can someone give me an example?
Thanks,
Steve
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
Hi just new here in the forum i have a question
is it a good practice to use autoincrement fields in an access database for master and detail? and for unique record identification. if not please tell why..
im currently using access and with autoincrement for unique record identification and for master and detail.. then there is a possibilities to transfer from access to mssql. will i encounter problems when migrating since i am using an autoincrement fields
in mssql is there an autoincrement fields like access?
dont be harsh ok.. just anoob trying to learn. :eek:
Im still learning my way around SQL and queries and i was wondering :
How do you get a SQL Table to autoincrement a Guid? (is it "Is Identity?" or "RowGuid"....)
How would i create a new row with a new Guid, and insert into the values i want without specifying the Guid?
How can I know that a column is auto incremented or not? What are the commands used to know this??
Thanks guys
Hi,
I have an existing table (without PK). I want to output my SELECT statement with an auto-increment on the 1st column like this:
# Lastname Firstname Address
1 Obama Clinton xxxxx
2 Hillary xxxx xxxxx
...
Can someone help me on this pls?
Appreciate it.
Thanks
joseph
Running an insert into a table where a column has a constraint for unique records
i have an autonumber pk that increments but numbers are not in sequence etc. if constraints are found
e.g.
10 records are inserted but only 2 are unique but there is a gap of 8 in the PK autoincrement value (it's like the unique records that were not inserted are still being recorded by the autoincrement).
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
Thanks,
Steve
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedI have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.
Beli
hi,
does anybody know how to create an identity column in a query without having to use the into clause?
for example, i want to do the following:
select *,identity(int,0,1) as myId
from myTable
but this is not allowed
thank you
i have a table for storing username and password.
the following are the fields in table
ID -int
UserName-nvarchar(50)
Password-nvarchar(50)
i dont want to have a separate field for id
i want to autincrement them
plz give me an idea
am using SQLServer Express
am new to this field so plz forgive me for this simple question
thanks
Shruthi
in mysql the primary key has the possibility tu autoincrement herself
can it be done in MS SQL ??? how
I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?
View 2 Replies View RelatedCan anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi All I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new record the id will be 21plese help me in this
View 3 Replies View Relatedhi,
Is their anybody knows how to make field in Sql Server autoincrement?
pls. help me.
Hi All,
This may sound quite easy for you, however I am a newbie in SQL.
So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.
So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1
Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2
The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.
can anyone help?
Thanks
Johann
Hi all, i am deleting rows of a table and reinserting new ones. This table has an auto increment primary key. what is the function used to set it to zero each time i delete the rows in order to rebegin counting from 1 when i refill the table. Thanks in advance...
View 7 Replies View RelatedI've been searching for an answer a long time now... but nowhere I found a clear solution...
Is it possible to implement autoincrement with SQLexpress edition?
I created a new table in sql server 2005. Primary key is autoincrement value of 1. But when I insert the row, it increments by 4. It goes like 1, 5, 9 etc..what could be the problem ?how can I correct it? Do I need to reseed it?
Thanks!!
looking for necessary syntax to alter table id to autoincrement adding identity statement, not sure on syntax for seed an increment, or if it is possible at all.
View 2 Replies View RelatedI noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?
T.G.
Hey guys,
I have 2 tables:
1. CommandCode( id AutoIncrement_integer, name string )
2. FooCode( FooID integer, CommandCodeID integer)
I have the following stored procedure
PROCEDURE InsertRows( CodeName VARCHAR )
INSERT INTO CommandCode ( name ) VALUES (CodeName)
Now right here I need to INSERT INTO the "FooCode" table using the same ID that was just added to the CommandCode table. How can I retrieve the last inserted autoincrement value for "id" in the CommandTable without doing SELECT id FROM CommandCode WHERE name = CodeName
END PROCEDURE
Is there an easy way to do what I described above? If possible I want it to comply with SQL standards (e.g. no MS SQL specific code)
Thanks!
Hi all!
Does anyone knows how to get if a column is autoincrement or not, using the DatabaseMetaData in java, or using de JTurbo driver for it?
Is there any other way to figure this out?
:confused:
Thank you very much in advance for any replies.
Hi All,
i wonder if i can get an bigint autoincrement field where the number begins with the current year + 1 autonumber
Does someone know if it is possible and if yes, how?
Already thanx.
Cheers Wim
I am creating a trigger in SQL Server that will activate when my Athelete table has something "Inserted"
So far it works except for the fact that it won't insert a null into the Equipment_ID field. I need it to autoincrement, or do the last number + 1...
CREATE TRIGGER TRG_NEW_EQUIPMENT1
ON ATHLETE AFTER INSERT
AS
BEGIN
INSERT INTO Equipment Equipment_ID, Equipment_Model, Equipment_Year, Equipment_Brand, Equipment_Color, Equipment_Condition_Rating)
VALUES ('Big Spin','2016','K2','Blue','5')
END;
GO
Hi,I'm enclosed a snippet of test code which highlights my problem. The Storedprocedure insertValue should insert text into the parent, then insert othertext into the child table but the 2 tables should auto increment in sync(i.e. so that they both end up with the same id numbers). I've tried takingthe auto increment out of the child table but then I don't know how to getthe right parent id into the child table.Any advice appreciated - this is my first database, so I'm just in thelearning process really. Code follows:CREATE TABLE Parent(id INTEGER DEFAULT AUTOINCREMENT,parenttext VARCHAR(16),PRIMARY KEY (id))!CREATE TABLE Child(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,childtext VARCHAR(16),FOREIGN KEY (childid) REFERENCES Parent(id),PRIMARY KEY (childID))!CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))BEGINinsert into parent (parenttext) values (p);insert into child (childtext) values (c);END!call insertValues('from parent', 'from child')!select * from parent, child where parent.id = child.childid!
View 5 Replies View RelatedI have a table in my sqlserver 2000 that has a field IDNO. i want thisfield to be my primary key. however i don't want this field to use theautoincrement feature. when i access this table from vb.net and try toadd a record this field is autoincrementing. how can i disable theautoincrement of this field yet serves this as my primary key?thanks in advance
View 4 Replies View Related